[FIXED] How to update a table without creating new rows

Issue

Imagine I have a set of key-value data from a primary key to values:

id	foo
1	abc
2	def
3	ghj

... that need to be updated in a table.

And I want to update all of these in one query. Naturally, upserts come to mind, which works quite well:

INSERT INTO my_table (id, foo) VALUES ((1, 'abc'), (2, 'def'), (3, 'ghj')) 
ON CONFLICT (id) DO UPDATE SET foo = excluded.foo;

This works fine, but what if I don't actually want to insert the row with id=3 when it doesn't already exist in the table my_table?

Solution

I don't see why you would need an INSERT at all if you just want to UPDATE the rows?

update my_table 
  set foo = v.foo 
from ( 
  VALUES (1, 'abc'), (2, 'def'), (3, 'ghj')
) as v(id, foo)
where v.id = my_table.id;

Answered By - a_horse_with_no_name

Answer Checked By - Terry (PHPFixing Volunteer)