Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sql-server

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.

like image 971
Espen Avatar asked Nov 10 '15 14:11

Espen


1 Answers

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)

like image 143
Roman Pekar Avatar answered Sep 22 '22 14:09

Roman Pekar