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?

Lukáš Lalinský · Accepted Answer · 2009-10-04 13:54:12Z

54

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

answered Oct 4, 2009 at 13:54

Lukáš Lalinský

41.5k6 gold badges109 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alan Jackson Over a year ago

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ý Over a year ago

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 Over a year ago

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 Over a year ago

When I first looked at this answer, I thought surely this won't work, but it did! Great answer and thank you.

Ajay Gabani Over a year ago

MySQL 5.5 does not support LIMIT in IN/ALL/ANY/SOME subquery.

Svetoslav Genov · Accepted Answer · 2012-04-23 14:55:58Z

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.

Collectives™ on Stack Overflow

update multiple rows using limit in mysql?

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related