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)