How to update a table without creating new rows-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

One thing I have already tried (and it works) is to use a source query which receives all of the source data as a json list and then inner joins to the existing table to throw away all the records that don't have an entry in my_table:

[
  {"id": 1, "foo": "abc"},
  {"id": 2, "foo": "def"},
  {"id": 3, "foo": "ghj"}
]

which is passed as the only parameter to this query:

WITH source AS (
SELECT my_table.id, x.foo FROM jsonb_to_recordset($1::jsonb) AS x(id int, foo text)
JOIN my_table ON x.id = my_table.id
)
INSERT INTO my_table (id, foo) 
(SELECT * FROM source) 
ON CONFLICT(id) DO UPDATE SET foo = excluded.foo