sqlsql-servert-sql

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

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.

Solution

  • 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)