Why Doesn't MySQL's `UPDATE` Statement Support a Two-Part `LIMIT` Clause?

MySQL: Difficulties with LIMIT in Multi-Row Updates

In MySQL, attempting to update multiple rows using the LIMIT clause can encounter errors. Consider the following query:

UPDATE messages SET test_read=1
WHERE userid='xyz'
ORDER BY date_added DESC
LIMIT 5, 5;

This query aims to update five rows based on the condition provided, skipping the first five rows (LIMIT 5, 5). However, this results in an error.

The working version of the query without the second LIMIT clause (LIMIT 5) successfully updates the first five rows meeting the condition. The error occurs because MySQL does not allow specifying a second LIMIT within an update statement.

Recommended Alternative

To overcome this limitation, a workaround is recommended:

UPDATE messages SET test_read=1
WHERE id IN (
SELECT id FROM (
SELECT id FROM messages
ORDER BY date_added DESC
LIMIT 5, 5
) tmp
);

This query achieves the same result by first identifying the desired rows using a subquery and then updating the appropriate rows based on the id field. This method allows for precise row selection and updating in MySQL.

The above is the detailed content of Why Doesn't MySQL's `UPDATE` Statement Support a Two-Part `LIMIT` Clause?. For more information, please follow other related articles on the PHP Chinese website!