How Can I Use MySQL's LIMIT Clause to Update a Specific Number or Range of Rows?

MySQL: Limit Clause for Multi-Row Updates

In MySQL, the LIMIT clause is commonly used to limit the number of rows selected from a table. However, it can also be employed to specify the maximum number of rows to be updated. However, certain constraints must be considered to prevent errors.

The provided query:

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

fails because the second parameter of LIMIT specifies the offset, not the limit. Hence, the query is asking MySQL to update rows after the 10th row (skip the first 5 rows, then apply the offset of 5).

To resolve this issue, use the following query:

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

This query correctly updates the first 5 rows.

If you wish to update a specific range of rows, such as the 6th to 10th, you can use a subquery:

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 uses a subquery to select the IDs of the rows in the specified range. Then, the main query updates those rows accordingly.

The above is the detailed content of How Can I Use MySQL's LIMIT Clause to Update a Specific Number or Range of Rows?. For more information, please follow other related articles on the PHP Chinese website!