How to Update Top 100 Records in SQL Server

SQL Server is a Relational database Management system which is developed by Microsoft and is one of the most used databases in the world. It provides the developer with a set of rich functionalities to create tables, insert data in them, and then manipulate and play with them as and when necessary.

In this article, we will be looking at how one can update the top 100 records and understand the various method along with he examples and so on.

Introduction of Update in SQL Server

In SQL Server, the UPDATE statement is used to modify existing records in a table. It allows us to change the values of one or more columns in a specified set of rows based on a specified condition.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Explanation:

• table_name: The name of the table we want to update.
  
• column1, column2, ...: The columns we want to update.
  
• value1, value2, ...: The new values we want to assign to the specified columns.
  
• WHERE condition: The condition that specifies which rows to update. If satisfied then all rows in the table will be updated.

Ways to Update Top Records in SQL Server

Let us start by creating a table and adding some sample data to the table. We will create a test table with an id and title field. The following query creates the table:

Query:

CREATE TABLE test(id INTEGER, title VARCHAR(100));

Now that we have the table, let's insert some data into the data. The following query inserts ten records in the test table:

Query:

INSERT INTO test (id, title)
VALUES
(1, 'Title 1'),
(2, 'Title 2'),
(3, 'Title 3'),
(4, 'Title 4'),
(5, 'Title 5'),
(6, 'Title 6'),
(7, 'Title 7'),
(8, 'Title 8'),
(9, 'Title 9'),
(10, 'Title 10');

Output:

Explanation: Now that we have the setup in place, lets now go forward to see how we can update the top 100 records of the table. We are going to have a look in this article at two methods to update the top 100 records of the table.

Method 1: Using TOP clause

The TOP clause is used to restrict the number of records returned by the query. Using this in conjunction with the UPDATE clause we can restrict the update to the first n records only.

The following code updates the top 3 records of the table.

Query:

update top (3) test set id=-10;

Output:

Explanation: As we can see in the above image the id of the top 3 records in the table has been updated to -10. The parenthesis surrounding the number is necessary.

Method 2: Using Common Table Expression (CTE)

We can make use of Common Table Expression (CTE) to select only the top n records from the table and later update them using the UPDATE clause. However, to make this work, we need to provide an ordering using the ORDER BY clause for the SQL engine to understand what records to return.

The following query first creates a CTE of the top 3 records ordered by the id field. The update statement uses the CTE to update the top 3 records as above:

Query:

WITH CTE AS 
( 
SELECT TOP 3 * 
FROM test 
ORDER BY id 
) 
UPDATE CTE SET id=-10;

Output:

Explanation: As we can see in the above image the id of the top 3 records in the table has been updated to -10.

Example of Update top 100 records in SQL Server

Let's now use the concepts we have learned in this article in a technical example.

First let's create the table and insert some data inside it. The following query creates an employee table and inserts nine records in it.

Query:

-- create
CREATE TABLE EMPLOYEE (
  empId int,
  name varchar(100),
  dept varchar(50)
);


-- insert
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (100, 'Clark', 'Engineering');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (5, 'Jill', 'Sales');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (6, 'Ava', 'Marketing');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (2, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (71, 'Tom', 'Sales');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (4, 'Jake', 'Sales');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (8, 'Ben', 'Marketing');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (9, 'Alice', 'Engineering');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (11, 'Mike', 'Marketing');

The above query creates an employee table with three fields and inserts the data for nine employees. The following query returns the initial data in the table:

SELECT * FROM EMPLOYEE;

Output:

Explanation: Now lets try to update the department of the top 3 employees ordered by employee id to 'Analytics'. For this we can make use of CTE as we did in method 2 to update the records.

The following query creates a CTE select the top 3 records in order of their employee id and then the UPDATE statement makes use of the CTE to update the record's department to 'Analytics':

WITH CTE AS 
(
    SELECT TOP 3 * 
      FROM EMPLOYEE
      ORDER BY empId
)
UPDATE CTE SET dept='Analytics';

Now let's see the current data in the table. I will use the following query to get the records ordered by the employee id:

SELECT * FROM EMPLOYEE
ORDER BY empId;

Output:

Explanation: As we can see in the below image the department of Dave, Jake and Jill has been updated to Analytics from Accounting, Sales and Sales respectively.

Conclusion

In this article we covered how we can update the top n records of the table in SQL Server. We had a chance to look at two different methods to go about doing this, first using the TOP clause and the other using CTE. We also how we can use the concepts we learned in this article to a real-life situation through the technical example.