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?
2 Answers2
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 ); Sign up to request clarification or add additional context in comments.
5 Comments
Alan Jackson
Do you really need the double nested select? In ms sql it would just be something like "Where id in (SELECT TOP 5 id FROM messages ORDER BY date_added DESC)"
Lukáš Lalinský
Yes, MySQL doesn't allow you do update a table that it selects from. The extra nested select forces it to save the results into a temporary table.
deed02392
When you ask
if you really must do it this way, are you implying there is a better alternative to updating records based on a rank given by the order of one of its fields?
Gareth Williams
When I first looked at this answer, I thought surely this won't work, but it did! Great answer and thank you.
Ajay Gabani
MySQL 5.5 does not support LIMIT in IN/ALL/ANY/SOME subquery.
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.
Comments
Explore related questions
See similar questions with these tags.