Is it possible to update rows from a key/value pair?

Question

Explaining by example:

UPDATE Table SET value=(22,55,99) WHERE id IN (2,5,9)

So the row with id=2, value is set to 22. And the row with id=5, value is set to 55. Etc.

Typist

Answer 1

You can use derived table:

update t set
    value = a.value
from Table as t
    inner join (values
        (22, 2),
        (55, 5),
        (99, 9)
    ) as a(id, value) on a.id = t.id

For me this is the most elegant way to do this and it's also easily extandable (you can add more columns if you like)

Opinionated

To use it in postgresql with psycopg2 I had to change a bit this expresion. Look at #71739800 – Fig

Answer 2

Yes, but not like you have:

UPDATE Table
SET value=case when id=2 then 22 when id=5 then 55 when id=9 then 99 end
WHERE id in (2,5,9)

If you have to do this for a large number of fields/records, you'd be better off just issuing a bunch of dedicated update queries.

Parlance

Answer 3

Use a CASE expression to chose update value:

UPDATE Table SET value = case id when 2 then 22
                                 when 5 then 55
                                 when 9 then 99
                         end
WHERE id IN (2,5,9)

Gyimah

Answer 4

As commented, I missed the sql-server tag. This is valid for MySQL

INSERT INTO Table (id, value)
VALUES (2, 22), (5, 55), (9, 99)
ON DUPLICATE KEY UPDATE value = VALUE(value)

This will update the value column with the VALUES data since the unique constraint on id will trigger the ON DUPLICATE KEY UPDATE section of the SQL query

For large updates you can fill an array with all your (id, value) pairs and implode it into the VALUES string

Post

The question is tagged "sql-server". I believe that ON DUPLICATE KEY UPDATE exists only in MySQL. Are you sure that it also exists for SQL Server? – Shuttlecock