CODEWITHSUNDEEP
mysqlsqlsql-update
halocursed
halocursed

Reputation: 2495

update multiple rows using limit in mysql?

    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?

Upvotes: 28

Views: 48559

Answers (2)

Svetoslav Genov
Svetoslav Genov

Reputation: 366

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.

Upvotes: 5

Lukáš Lalinský
Lukáš Lalinský

Reputation: 41316

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

Upvotes: 54

Related Questions