CODEWITHSUNDEEP
sqlpostgresql
piko agusta
piko agusta

Reputation: 33

How to update two columns in one column with two different ID and different value in single SQL query?

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions