单查询插入多条外键关联记录并同步更新源表外键的实现方案

我想要实现一条查询语句，原子化完成表规范化操作：将另一张表的初始值填充到新表中，同时为源表新增外键引用指向新表记录。

显然从一张表填充另一张表可以使用标准INSERT INTO ... SELECT ...语法，但我同时需要更新「源表」中的外键字段，使其关联到「新表」中刚插入的对应记录。

待迁移原始表结构 #

CREATE TABLE companies (
  id INTEGER PRIMARY KEY,
  address_line_1 TEXT,
  address_line_2 TEXT,
  address_line_3 TEXT
)

迁移目标表结构 #

CREATE TABLE addresses (
  id INTEGER PRIMARY KEY,
  line_1 TEXT,
  line_2 TEXT,
  line_3 TEXT
)
CREATE TABLE companies (
  id INTEGER PRIMARY KEY,
  address_id INTEGER REFERENCES addresses(id)
)

初始尝试的错误方案 #

我最开始考虑用CTE实现，写法如下：

WITH new_addresses AS (
  INSERT INTO addresses (line_1, line_2, line_3)
  SELECT address_line_1, address_line_2, address_line_3
  FROM companies
  RETURNING id, companies.id AS company_id -- 无法生效
)
UPDATE companies
SET address_id = new_addresses.id
FROM new_addresses
WHERE new_addresses.company_id = companies.id

但实际运行发现RETURNING子句只能返回插入记录的字段，无法获取原companies表的id，因此该写法无法生效。

最终可用实现方案 #

目前可行的方案要么是使用PLSQL，要么结合数据的领域特征分多步完成，我当前的实现方案如下：

-- 第一步：保证插入的地址记录ID和关联的公司记录顺序一致
INSERT INTO addresses (line_1, line_2, line_3)
SELECT address_line_1, address_line_2, address_line_3
FROM companies
ORDER BY id;

-- 第二步：通过行号关联更新公司表的外键字段
WITH address_ids AS (
  SELECT id AS address_id, ROW_NUMBER() OVER(ORDER BY id) AS idx
  FROM addresses
), company_ids AS (
  SELECT id AS company_id, ROW_NUMBER() OVER(ORDER BY id) AS idx
  FROM companies
), company_address_ids AS (
  SELECT company_id, address_id
  FROM address_ids
  JOIN company_ids USING (idx)
)
UPDATE companies
SET address_id = company_address_ids.address_id
FROM company_address_ids
WHERE id = company_address_ids.company_id

该方案的局限性在于需要依赖addresses表和companies表的记录数完全一致，不过该查询属于表首次创建时的一次性执行语句，实际场景下可以满足需求。

内容的提问来源于stack exchange，提问作者Cechner