Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
LIMIT without ORDER BY is dangerous. LIMIT 2 for example will update 2 rows but which 2 (if you have 7 that pass the WHERE conditions)? –  ypercube Feb 18 at 18:39
    
@ypercube It doesn't actually matter which ones are updated, but you're probably right, ORDER BY would make it more predictable. –  user2581799 Feb 18 at 18:43
    
Does the order that the UPDATE statements are executed matter? –  ypercube Feb 18 at 18:44
    
@ypercube Nope, it won't matter what order they happen in either. –  user2581799 Feb 18 at 18:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.