update a table from insert result

Question

For the table B, there is a column named a_id which is the id of the table A. So a_id is the foreign key pointing to table a, but it is just a integer column and has no foreign constraint is set on it.

For each row in table B, we need to give the column a_id an integer value by creating a new record in table A.

The goal to do all below steps in one SQL.

Insert all of data into table A:

insert into table A (name) values ('abc'), ('def'), ... returning id
Buck update a_id of each row in table B with the id(each id should be used once only) returned from step 1

update table B set a_id = id(from previous insert statement)

Have tried something like:

update table B set a_id = (select ia.id from ia
(insert into table A (name) values ('abc'), ('def'), ... returning id) as ia)

But this gives a syntax error ERROR: syntax error at or near "into".

How to do it with one SQL?

[See here](https://stackoverflow.com/questions/6560447/can-i-use-return-value-of-insert-returning-in-another-insert) for a basic answer to your question. But, it doesn't seem to make sense to use `returning id` in your `insert` query, as you are inserting multiple records. — Tim Biegeleisen, Sep 21 '18 at 08:16
Your insert returns more than one row. Which of those IDs should be used for the UPDATE? — , Sep 21 '18 at 08:24
Please **[edit]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [Formatted text](https://meta.stackoverflow.com/a/251362) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question - do **not** post code or additional information in comments) — , Sep 21 '18 at 08:25
Your question is unclear because you have multiple ids being returned from the `insert` but a scalar subquery for the `update`. — Gordon Linoff, Sep 21 '18 at 10:47
You are mixing business key(name) with (artificial key) id. In order to update second table you have to join using name(if exists). Otherwise it is impossible to determine how the two tables are interconnected. Please post https://dbfiddle.uk demo with both table structures and sample data. — Lukasz Szozda, Jun 25 '19 at 20:08
Are you sure you want to *update* values in table B? This would imply that you already have the number of rows being inserted in table B. Do you actually need to *insert* records with just these IDs into table B? — Steve Chambers, Jun 26 '19 at 12:24
If possible make one trigger for table A with AFTER INSERT, in that trigger update table B. — Kishan, Jun 29 '19 at 07:35

score 1 · Answer 1 · answered Sep 21 '18 at 10:44

If you only insert a single row, you can use a data modifying CTE

with new_row as (
  insert into table_A (name) values ('abc')
  returning id
)
update table_b
  set a_id = (select id from new_row)
where ?????; -- surely you don't want to update all rows in table_b

However the above will fail if your insert more than one row in the first statement.

It is unclear to me, which rows in table_b would need to be updated in that case.

What about using `update...from` from the result ids returned from buck insert? — canoe, Sep 25 '18 at 09:45

Lie Ryan · Answer 2 · 2019-07-02T13:26:04.077

This is not exactly one query, but if your concern is really just to avoid race conditions/transaction isolation anomalies resulting from a naive approach of doing this with multiple queries, then this should do it:

-- assign tableA's future primary keys into a_id without 
-- creating entries on tableA, pg_get_serial_sequence()
-- should retrieve tableA's primary key generator
UPDATE tableB
    SET a_id = nextval(pg_get_serial_sequence('tableA', 'id'))
    WHERE a_id IS NULL;

-- insert into tableB, with explicitly specified primary keys, 
-- note that this doesn't increment tableA's sequence as that 
-- has already been done in the previous operation by nextval()
INSERT INTO tableA(id, name)
    SELECT a_id, name FROM tableB;

Note that you should still wrap this in a transaction to ensure that tableB's updates gets rolled back if the insertion fails for any reason. Alternatively, since the two operations above are idempotent, they are safe to retry concurrently even without a transaction.

update a table from insert result

2 Answers2