Reputation: 5175
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?
Upvotes: 8
Views: 11155
Reputation: 121
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)
Upvotes: 12
Reputation:
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)
Upvotes: 16
Reputation: 406135
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?
Upvotes: 2