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?

score 54 · Accepted Answer · answered Oct 04 '09 at 13:54

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 04 '09 at 13:54

Lukáš Lalinský

40,587
6
104
126

1

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)" – Alan Jackson Jun 08 '11 at 12:38
19

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. – Lukáš Lalinský Jun 12 '11 at 12:09
2

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? – deed02392 Oct 23 '13 at 19:26
When I first looked at this answer, I thought surely this won't work, but it did! Great answer and thank you. – Gareth Williams Aug 12 '14 at 07:24
1

MySQL 5.5 does not support LIMIT in IN/ALL/ANY/SOME subquery. – Ajay Gabani Dec 31 '15 at 07:02

score 5 · Answer 2 · answered Apr 23 '12 at 14:55

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.

update multiple rows using limit in mysql?

2 Answers2

Linked

Related