I need to perform a large number of updates at the same time. Each update needs to only affect up to a specific number of rows even though the
WHERE clause might match to many more rows. So far, I'm accomplishing this with 'LIMIT' and submitting each query individually. My queries look like this:
UPDATE data SET c=1 WHERE c=2 AND g=8 LIMIT 1 UPDATE data SET c=2 WHERE c=4 AND g=2 LIMIT 1 UPDATE data SET c=4 WHERE c=3 AND g=5 LIMIT 1 ...
This accomplishes exactly what I need, but becomes very slow when the number of updates reaches hundreds or thousands (and the table
data is even bigger). How can I make this faster?
I thought about doing something like this:
CREATE TABLE changes (c_old INT, c_new INT, g INT) INSERT INTO changes (c_old, c_new, g) VALUES (2,1,8),(4,2,2),(3,4,5)... UPDATE data INNER JOIN changes ON data.c=changes.c_old, data.g=changes.g SET data.c=changes.c_new DROP TABLE changes
Because four queries are probably faster than thousands even though the second query might be very long.
The first problem with that is that it doesn't allow me to accomplish what I was doing with the
LIMIT. This changes every match rather than just the number I need changed.
The second problem is that it just feels messy to create and drop a table every time, but that's something I could live with.
Any ideas? How can I make this faster? Is there really no faster way to do this particular thing than submitting each update individually?