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

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

Answer 2

http://bugs.mysql.com/bug.php?id=42415

The documentation states that any UPDATE statement with LIMIT clause is considered unsafe since the order of the rows affected is not defined: http://dev.mysql.com/doc/refman/5.1/en/replication-features-limit.html

However, if "ORDER BY PK" is used, the order of rows is defined and such a statement could be logged in statement format without any warning.