Reputation: 33
I have a table Item
:
id | price
---+--------
1 | 20000
2 | 30000
3 | 40000
4 | 43000
How to update the price column id(1) = 1000, id(2) = 2000 in a single SQL query?
Upvotes: 2
Views: 49
Reputation: 1271241
Use case
:
update items
set price = (case when id = 1 then 1000 when id = 2 then 2000 else price end)
where id in (1, 2);
You can also write this using a from
clause:
update items
set price = v.price
from (values (1, 1000), (2, 2000)) v(id, price)
where v.id = items.id;
This is a bit safer as well, ensuring that the ids really do match an no extra rows are accidentally updated.
Upvotes: 3