Example of Updating Top 100 Records in SQL

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.

-- 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;

The following is the initial data:

Now let’s try to update the department of the top 3 employees ordered by employee id to ‘Analytics‘. For this, we are going to make use of the concepts learned in method 1. We will use make use of the LIMIT clause to get the desired results. In the following query, we make use of the ORDER BY clause to order the employees by their employee id and the LIMIT clause in the end to restrict the update statement to the top 3 records:

UPDATE EMPLOYEE SET dept='Analytics'
ORDER BY empId 
LIMIT 3;

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

Explanation: The output of the query is an updated EMPLOYEE table where the department for the top 3 employees (based on ascending empId) is set to ‘Analytics’.

As our systems get more complex and complex, there is often a need to update the underlying data to accommodate the evolution of the system. SQL provides a variety of ways to update the data so that the system developer can manipulate the data in whatever way necessary. In this article, we will be looking at how one can update the top 100 records using SQL.

Note: I will be using MySQL for this article. Also, for demonstration, I will only update the top 3 rows. However, the steps will be the same irrespective of the number of rows.