Is it possible to use UPDATE query with LIMIT in MySQL?

Yes, it is possible to use UPDATE query with LIMIT in MySQL. Let us see how.

For our example, we will first create a table. The CREATE command is used to create a table.

mysql>CREATE table tblUpdateLimit
-> (
-> id int,
-> name varchar(100)
-> );
Query OK, 0 rows affected (0.53 sec)

Records are inserted with the help of INSERT command.

mysql>INSERT into tblUpdateLimit values(1,'John');
Query OK, 1 row affected (0.54 sec)

mysql>INSERT into tblUpdateLimit values(2,'Carol');
Query OK, 1 row affected (0.12 sec)

mysql>INSERT into tblUpdateLimit values(3,'Smith');
Query OK, 1 row affected (0.10 sec)

mysql>INSERT into tblUpdateLimit values(4,'Kayle');
Query OK, 1 row affected (0.44 sec)

mysql>INSERT into tblUpdateLimit values(5,'David');
Query OK, 1 row affected (0.13 sec)

mysql>INSERT into tblUpdateLimit values(6,'Jason');
Query OK, 1 row affected (0.18 sec)

mysql>INSERT into tblUpdateLimit values(7,'Larry');
Query OK, 1 row affected (0.15 sec)

mysql>INSERT into tblUpdateLimit values(8,'Serhat');
Query OK, 1 row affected (0.15 sec)

mysql>INSERT into tblUpdateLimit values(9,'Winny');
Query OK, 1 row affected (0.18 sec)

To display the above table, here is the query.

mysql> SELECT *from tblUpdateLimit;

The following is the output.

+------+--------+
| id   |name    |
+------+--------+
| 1    | John   |
| 2    | Carol  |
| 3    | Smith  |
| 4    | Kayle  |
| 5    | David  |
| 6    | Jason  |
| 7    | Larry  |
| 8    | Serhat |
| 9    | Winny  |
+------+--------+
9 rows in set (0.00 sec)

Let us now see the syntax to use UPDATE query with limit.

UPDATE yourTableName SET column_name='some value’'
WHERE column_name1 IN (
SELECT column_name1 FROM (
select column_name1 from yourTableName order by column_name1 asc limit integerValue,integerValue)
anyAliasName );

Implementing the query now to fulfil our purpose and using it to set the name ‘Adam’, with limit 7.

mysql> UPDATE tblUpdateLimit SET name = 'Adam'
-> WHERE id IN (
SELECT id FROM ( select id from tblUpdateLimit order by id asc limit 0,7)l);
Query OK, 7 rows affected (0.27 sec)
Rows matched: 7 Changed: 7 Warnings: 0

Check whether the table is updated or not.

mysql> SELECT *from tblUpdateLimit;

Here is the output.

+------+--------+
| id   | name   |
+------+--------+
| 1    | Adam   |
| 2    | Adam   | 
| 3    | Adam   |
| 4    | Adam   |
| 5    | Adam   |
| 6    | Adam   |
| 7    | Adam   |
| 8    | Serhat |
| 9    | Winny  |
+------+--------+
9 rows in set (0.00 sec)