update multiple rows using limit in mysql?

Question

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

I am trying to use this query to update a set of 5 rows using limit but mysql is showing an error..The one below is working

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

why is the first one not working?

Answer 1

Why would you need 2 parameters for the LIMIT statement if you only want to limit to 5 results? LIMIT 5,5 means limit to 5 records from the 5th, but since you are doing and UPDATE statement it does not make sense to be using it this way.

Answer 2

 limit 0, 5

syntaxis is "limit from, count"

Answer 3

If you really must do it this way, you can use something like this:

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