Geek Answers Handbook

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

Example explanation:

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

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

+6
source share
3 answers

You can use a view:

 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 is also easy to expand (you can add more columns if you want)

+14
source

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 need to do this for a large number of fields / records, you would be better off just releasing a bunch of highlighted update requests.

+1
source

Use the CASE expression to select the 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)
0
source

More articles:


All Articles