How to Update Top N Records in MySQL - Coding

MySQL is a free and open-source relational database management system written in C and C++ that is extremely popular among developers. Like other relational database management systems, MySQL provides a variety of rich features to create databases and tables, insert data in them, and further manipulate them as the system evolves.

Here, we will be looking at how one can update the top 100 records using SQL. Understanding this will allow developers to easily manipulate data in their existing tables.

Updating Top N Records in MySQL

Let’s begin by creating a sample table and inserting some data for demonstration purposes. Assume we have a table named ‘test' with columns ‘id' and ‘title'.

MySQL

CREATE TABLE test (
id INTEGER, title VARCHAR(100)
);
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');

The following is the current data in the table:

id	title
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

Now that we have the setup in place, let 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.

Note: For demonstration, I will only update the top 3 rows. However, the steps will be the same irrespective of the number of rows.

Method 1: Using LIMIT clause

The LIMIT clause is used to restrict the records returned by the query. We can make use of the LIMIT clause in conjunction with the UPDATE clause to restrict the query to only update the top n records. 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 updates the top 3 records depending on the ascending order of the id column.

Query:

UPDATE test 
SET id=-10 
ORDER BY id 
LIMIT 3;

Output:

id	title
-10	Title 1
-10	Title 2
-10	Title 3
4	Title 4
5	Title 5
6	Title 6
7	Title 7
8	Title 8
9	Title 9
10	Title 10

Method 2: Using Row Number

This is a little bit of a hack and is a little bit complicated than the above method but it does not need the user to define an ordering.

First, we will start by altering the table to add a column which will be used to store the row number value. We will add a column named row_num of integer type to the table.

ALTER TABLE test ADD row_num int;

Now we will define a variable which we will use to assign the row number to the record. We will initialize it with 0 and use it in the update statement to assign value to the newly added row_num column.

SET @row_number = 0;
UPDATE test
SET row_num= (@row_number:=@row_number + 1);

We will make use of the row_num column in the WHERE clause to filter out the required records

UPDATE test
SET id=-10
WHERE row_num<=3;

Finally, we will drop the extra column using the ALTER clause, so that there is no change in the original schema of the table.

ALTER TABLE test DROP COLUMN row_num;

Output:

id	title
-10	Title 1
-10	Title 2
-10	Title 3
4	Title 4
5	Title 5
6	Title 6
7	Title 7
8	Title 8
9	Title 9
10	Title 10

Example of Update top N records in MySQL

Updating Top Employees’ Department to ‘Analytics’

Let’s now use the concepts we have learned in this article as 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.

MySQL

CREATE TABLE EMPLOYEE (
  empId INT,
  name VARCHAR(100),
  dept VARCHAR(50)
);

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');
SELECT * FROM EMPLOYEE;

The following is the initial data:

empId	name	dept
100	Clark	Engineering
5	Jill	Sales
6	Ava	Marketing
2	Dave	Accounting
71	Tom	Sales
4	Jake	Sales
8	Ben	Marketing
9	Alice	Engineering
11	Mike	Marketing

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.

Query:

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

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.

empId	name	dept
2	Dave	Analytics
4	Jake	Analytics
5	Jill	Analytics
6	Ava	Marketing
8	Ben	Marketing
9	Alice	Engineering
11	Mike	Marketing
71	Tom	Sales
100	Clark	Engineering

Expalantion: The output of the provided example is an update in the department field for the top 3 employees, ordered by their employee ID, to ‘Analytics‘. The UPDATE statement utilizes the ORDER BY clause to sort the employees based on their employee ID, and the LIMIT clause ensures that only the top 3 records are updated. This approach allows for targeted updates on specific rows, providing control over which records are affected, in this case, transforming the departments of Dave, Jake, and Jill to ‘Analytics’.

Conclusion

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

Reffered: https://www.geeksforgeeks.org

Databases

Related
MySQL SELECT Statement
How to Select Row With Max Value in MySQL?
How to Rename a Column in MySQL?
How to Efficiently Convert Rows to Columns in SQL?
How to Remove Duplicate Records Except a Single Record in MySQL?

Type:	Geek
Category:	Coding
Sub Category:	Tutorial
Uploaded by:	Admin
Views:	12