How to Update a Specific Number of Rows in MySQL Using LIMIT?

Update Multiple Rows with LIMIT in MySQL

You are attempting to update a specific number of rows using a LIMIT clause in a MySQL query, but encountering an error. Let's understand why your initial query may have failed and how to fix it.

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

The above query is not valid because LIMIT expects a positive integer representing the offset from the beginning of the result set, and then the number of rows to be updated. In your query, you have specified both an offset (5) and a limit (5), which is incorrect.

To update a specific number of rows starting from an offset, you can use a subquery to select the rows to be updated and then update them. Here's a revised query:

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

In this query, a subquery is used to select the IDs of the rows to be updated, starting from the 5th row in descending order of the date_added column. The main query then uses the IN clause to update only these specific rows.

The above is the detailed content of How to Update a Specific Number of Rows in MySQL Using LIMIT?. For more information, please follow other related articles on the PHP Chinese website!