CODEWITHSUNDEEP
sqlpostgresqluniqueupsert
Tara
Tara

Reputation: 549

SQL insert multi rows if do not exists else update a specific column with COALESCE

I have a table with two columns: id, age where id is the primary key

I know how to insert ONE new row for new primary key else update the age with the new value if the age value is not null using the following sql:

insert into obj (id, age) 
values (2, 42) 
on conflict (id) do 
update set age = coalesce(42, obj.age)

but how do I do it with multiple rows? For example:

insert into obj (id, age) 
values (2, 42), (3, 43), (5, 60) 
on conflict (id) do 
update set age = coalesce(???, obj.age)

the question is what should I put in the '???' in COALESCE?

I thought some one suggested using COALESCE(values(age), obj.age, I tried but it didn't work (syntax error).

Upvotes: 2

Views: 301

Answers (1)

klin
klin

Reputation: 121794

insert into obj (id, age) 
values (2, 42), (3, 43), (5, 60) 
on conflict (id) do 
update set age = coalesce(excluded.age, obj.age)

Note the use of the special record excluded. Per the documentation:

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.

Upvotes: 1

Related Questions