MySQL Forums
Forum List  »  Quality Assurance

Single-query multi-row indepdent updates
Posted by: Olaf van der Spek
Date: January 30, 2008 04:38AM

Hi,

It's about http://bugs.mysql.com/bug.php?id=1873
The request has been closed and I think this isn't justified.

Summary:
I'd like to have a single query that's equivalent to the following queries (and that scales to 1000+ rows and works in strict mode), where the numbers are independent of eachother.

The work around is quite nice, but doesn't work in strict mode and does an insert when the row doesn't exist which I'd like to avoid.

What do you think of this request and could the request be opened again?

update set rank = 1 where pid = 1;
update set rank = 2 where pid = 4;
update set rank = 3 where pid = 9;

Work around:
INSERT t (rank, pid) VALUES (1, 1), (2, 4), (3, 9)
ON DUPLICATE KEY UPDATE rank = VALUES(rank)

Options: ReplyQuote


Subject
Views
Written By
Posted
Single-query multi-row indepdent updates
3200
January 30, 2008 04:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.