Situation
I have two child tables that each have a foreign key to a parent table in PostgreSQL. Child_b also has a foreign key to child_a. Child_a previously was optional but is now required, at least one per parent. I want to insert rows into child_a, and then update existing rows in child_b’s foreign key to the newly inserted child_a ids.
Solution
WITH child_b_missing_child_a AS
SELECT
b.id AS b_id,
a.parent_id
FROM child_b b
LEFT JOIN child_a a ON b.child_a_id = a.id
WHERE a.id IS NULL
),
child_a_inserts AS (
INSERT INTO child_a
(name, status, updated_at, parent_id)
SELECT
'placeholder', 'pending', now(), parent_id
FROM (select distinct parent_id from child_b_missing_child_a) as parents
RETURNING id, parent_id
)
UPDATE child_b b
SET child_a_id = m.child_a_id, updated_at = now(), update_authenticated_by = 0, updated_by = 0
FROM (select a.id as a_id, b_id
from child_a_inserts a
join child_b_missing_child_a missing on a.parent_id = missing.parent_id) as m
WHERE b.id = m.b_id and b.child_a_id is null;