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

MySQL: Using LIMIT to Update Multiple Rows

In MySQL, the LIMIT clause allows you to restrict the number of rows affected by a query. However, when using LIMIT with the UPDATE statement, a common error arises when attempting to update a specific range of rows.

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, starting from the fifth row, in the "messages" table where "userid" equals "xyz". However, this query will result in an error.

To understand why, it's important to note that the LIMIT clause in this context defines an offset and a maximum number of rows. In this example, it retrieves and limits the number of rows displayed rather than updating them. To update a specific range of rows, a subquery can be used instead:

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

In this modified query, a subquery is used to select the IDs of the five rows that need to be updated. The main UPDATE statement then matches these IDs and performs the update. This ensures that only the desired rows are modified.

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