如何用单条SQL语句实现表B数据迁移至表A并更新B的pid字段

完全可行，核心是在插入表A的同时，把生成的id和表B的原记录关联，一次性完成插入和更新操作，避免两次遍历表B。不同主流数据库的实现语法如下：

Oracle 写法 #

利用WITH子句结合INSERT ... RETURNING，先将B的数据插入A，再用插入结果更新B的pid字段：

WITH inserted AS (
  INSERT INTO A (id, f1, f2, f3)
  SELECT mysequence.nextval, f1, f2, f3 FROM B
  RETURNING id, f1, f2, f3
)
UPDATE B b
SET pid = (SELECT id FROM inserted i WHERE i.f1 = b.f1 AND i.f2 = b.f2 AND i.f3 = b.f3);

PostgreSQL 写法 #

PostgreSQL支持直接在UPDATE的FROM子句中嵌套INSERT ... RETURNING，逻辑更简洁：

UPDATE B b
SET pid = inserted.id
FROM (
  INSERT INTO A (id, f1, f2, f3)
  SELECT nextval('mysequence'), f1, f2, f3 FROM B
  RETURNING id, f1, f2, f3
) inserted
WHERE inserted.f1 = b.f1 AND inserted.f2 = b.f2 AND inserted.f3 = b.f3;

MySQL 8.0.20+ 写法 #

MySQL 8.0.20及以上版本支持INSERT ... RETURNING，结合WITH子句实现：

WITH inserted AS (
  INSERT INTO A (id, f1, f2, f3)
  SELECT mysequence.nextval, f1, f2, f3 FROM B
  RETURNING id, f1, f2, f3
)
UPDATE B b
JOIN inserted i ON b.f1 = i.f1 AND b.f2 = i.f2 AND b.f3 = i.f3
SET b.pid = i.id;

关键注意事项 #

• 必须确保表B中f1、f2、f3的组合是唯一的，否则无法准确匹配表A中对应的新插入记录，会引发更新错误。
• 不同数据库的语法细节有差异，需根据实际使用的数据库调整语句。

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