Updating multiple rows with different values

Question

I got this table in my MySQL database, 'users'. It has the fields 'id' and 'value'.

Now, I want to update lots of rows in this table with a single SQL query, but many rows should get a different value. Currently, I'm using this:

UPDATE users
    SET value = CASE id
        WHEN 1 THEN 53
        WHEN 2 THEN 65
        WHEN 3 THEN 47
        WHEN 4 THEN 53
        WHEN 5 THEN 47
    END
WHERE id IN (1,2,3,4,5)

This works. But I feel I could do some optimization since there are only about 3 or 4 different values I'm assigning to the rows. As you can see, right now these are 47, 53 and 65. Is there a way I can update all rows that get the same value simultaneously within the same query? Or, is there another way I can optimize this?

Answer 1

I would just do this with a few different UPDATE statements.

UPDATE users
    SET value = 53
WHERE id = 1;


UPDATE users
    SET value = 65
WHERE id = 2;

...

This seems simplest if you only have 5 or 6 values to set on multiple rows each. Or is there some specific reason that you need to do this in one query?

Answer 2

Rather than doing case variable when value then ..., try doing case when condition then ... - like so:

UPDATE users
    SET value = CASE 
        WHEN id in (1,4) THEN 53
        WHEN id = 2 THEN 65
        WHEN id in (3,5) THEN 47
    END
WHERE id IN (1,2,3,4,5)

Answer 3

I'm making the assumptions that a) you are running these queries from an app somewhere, not directly on the MySQL server b) you have tried running each individual query by itself and noticed that it was taking a long time to finish.

You could combine all your updates into a single call to the database. For instance, in PHP:

mysql_query("UPDATE users SET value = 53 WHERE id = 1; UPDATE users SET value = 65 WHERE id = 2; ... etc");

That way, the overhead of making the individual call (and trip to the database and back) won't happen for each individual update.