Can UPDATE queries be used with LIMIT in MySQL?

Yes, you can use UPDATE queries with LIMIT in MySQL. Let's see how.

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

mysql>CREATE　table　tblUpdateLimit
->　(
->　id　int,
->　name　varchar(100)
->　);

The records are inserted with the help of the INSERT command.

mysql> INSERT into tblUpdateLimit values(1,'John');
mysql> INSERT into tblUpdateLimit values(2,'Carol');
mysql> INSERT into tblUpdateLimit values(3,'Smith');
mysql> INSERT into tblUpdateLimit values(4,'Kayle');
mysql> INSERT into tblUpdateLimit values(5,'David');
mysql> INSERT into tblUpdateLimit values(6,'Jason');
mysql> INSERT into tblUpdateLimit values(7,'Larry');
mysql> INSERT into tblUpdateLimit values(8,'Serhat');
mysql> INSERT into tblUpdateLimit values(9,'Winny');

To display the above table, this 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)

Now let's see the syntax of using an UPDATE query with a 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);

Now, let's execute the query to achieve our goal and use it to set the name 'Adam' (limited to7).

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

Check if the table has been updated.

mysql> SELECT　*from tblUpdateLimit;

This 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)