从插入结果更新表格
从插入结果更新表格
对于表B，有一个名为a_id的列，它是表A的id。因此，a_id是指向表A的外键，但它只是一个整数列，并且没有设置外键约束。
对于表B中的每一行，我们需要通过在表A中创建新记录来给列a_id赋一个整数值。
目标是在一个SQL中完成以下所有步骤。
- 将所有数据插入表A：
insert into table A (name) values ('abc'), ('def'), ... returning id - 使用从步骤1返回的id（每个id只能使用一次）更新表B中每一行的a_id：
update table B set a_id = id(来自先前插入语句)
尝试了类似以下的方法：
update table B set a_id = (select ia.id from ia
(insert into table A (name) values ('abc'), ('def'), ... returning id) as ia)
但是这会导致语法错误ERROR: syntax error at or near "into"。
如何在一个SQL中实现它？
问题出现的原因是：当在第一个语句中插入多行数据时，上述的解决方法将无法正常工作。不清楚在这种情况下应该更新table_b中的哪些行。
解决方法是：可以尝试使用"update...from"语句从插入结果的id中进行更新。
以下是一个中文版的文章：
如果只插入一行数据，可以使用数据修改的公共表表达式（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 ?????; -- 确保你不想更新 table_b 中的所有行
然而，如果在第一个语句中插入了多行数据，则上述方法将无法正常工作。
对于这种情况下应该更新table_b中的哪些行，还不清楚。
那么，为什么不尝试使用"update...from"语句从返回的插入结果id中进行更新呢？
问题的原因是为了避免由于使用多个查询的简单方法而导致的竞争条件/事务隔离异常。解决方法是使用上述代码来更新一个表。
上述代码的作用是将tableA的未来主键分配给a_id，而不会在tableA中创建条目。其中，pg_get_serial_sequence()函数用于检索tableA的主键生成器。然后，代码将a_id为空的行更新为tableB中的下一个值。接下来，代码将tableB中的数据插入到tableA中，同时显式指定主键。
需要注意的是，为了确保如果插入失败时tableB的更新可以回滚，仍然应将这些操作包裹在一个事务中。或者，由于上述两个操作是幂等的，即使没有事务，它们也可以安全地同时重试。