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?

You could store the mapping in a separate table and join to it. — Pointy, Oct 07 '10 at 14:04
@Benoit because while I know SQL, I don't know MySQL and therefore would not necessarily be able to construct a correct example. — Pointy, Oct 07 '10 at 14:33

score 16 · Accepted Answer · answered Oct 07 '10 at 14:19

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)

score 12 · Answer 2 · answered Jan 13 '16 at 14:01

12

Assuming id is unique or primary...

insert into users 
  (id,value) 
VALUES 
  (1,53),(2,65),(3,47),(4,53),(5,47)
on duplicate key update
value=VALUES(value)

answered Jan 13 '16 at 14:01

mainbow

121
1
2

2

I don't know why this got so few votes. It's perfect! – Matteo B. Jun 21 '16 at 10:01

score 2 · Answer 3 · answered Oct 07 '10 at 14:07

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?

Updating multiple rows with different values

3 Answers3

Linked