CODEWITHSUNDEEP
sqlpostgresql
discodowney
discodowney

Reputation: 1507

Updating multiple records with different columns set

I want to know if I can/how to update multiple records with different columns set in a single query.

So I have a table

Test {
   id integer
   name varchar
   age integer
}

Then for test1 and test 2 i have

test1 = Test {id=1, name="Joe"}
test2 = Test {id=2, age=22}

Is it possible to have a single query to update both of these even though each is updating a different column?

If i have multiple objects that all have the same fields something like the following works:

UPDATE test as t 
SET id = u.id, age = u.age, name = u.name 
FROM (VALUES (1, 21, 'Ronan'),(2, 65, 'Conor') ) 
AS u(id, age, name) 
WHERE u.id = t.id

Anyone able to clear this up for me? Im using Postgres if that makes a difference

Upvotes: 0

Views: 125

Answers (1)

Belayer
Belayer

Reputation: 14936

An update always sets each column listed and none not listed. So there is no direct way of updating different columns in a single statement0. However you can do so indirectly by specifying null for the columns you do not want updated then using the coalesce function to set those columns to their existing values (it cannot however set a value to null). Something like: (see demo)

with new_val (id, name, age) as 
     ( values (1, null, 40) 
            , (2,'Paul',null) 
     ) 
update test  t
   set name = coalesce( nv.name, t.name) 
     , age  = coalesce( nv.age, t.age) 
  from new_val nv
 where t.id = nv.id;

The question becomes: Is it really worth it? This could become quite messy if needed on more than a couple columns and/or rows. Seems specific updates for specific columns is both easier and safer.

Upvotes: 1

Related Questions