I have two tables Table_A and Table_B. How can I write a conditional SQL that does the following logic
If table A records match table B records on id
then
delete records from table A and Insert records into Table B
How can I do this with SQL most likely using with
delete from Table_A where Exists (select a.id from TABLE_A
join TABLE_B as b on a.id = b.id)
The Insert is:Insert into Table_A (id) select id from TABLE_B
Use a CTE to catch the ids of the deleted records, and re-join these with the b records:
WITH del AS (
DELETE FROM a
WHERE EXISTS ( SELECT *
FROM b
WHERE b.id = a.id
)
returning *
)
INSERT INTO a (id, x, y, z)
SELECT id, x, y, z
FROM b
WHERE EXISTS (
SELECT *
FROM del
WHERE del.id = b.id
);
BTW: you should have very good reasons (such as wanting to activate the triggers) to prefer delete+insert to a update.
