如何更新关联表字段为对应新外键值，替换原有业务主键关联关系

前置准备 #

• 操作前先备份Persons和Books两个表的全量数据，避免误操作导致数据损坏
• 调整Books表owner字段的数据类型，确保和Persons表uid字段的类型完全一致（原来的owner是存储userCode的字符串类型，需要改成和uid匹配的数值类型）

批量更新SQL #

1万条数据量级很小，直接用关联更新语句即可秒级完成，不同数据库的写法如下：

MySQL

UPDATE Books b
INNER JOIN Persons p ON b.owner = p.userCode
SET b.owner = p.uid;

PostgreSQL

UPDATE Books b
SET owner = p.uid
FROM Persons p
WHERE b.owner = p.userCode;

SQL Server

UPDATE b
SET b.owner = p.uid
FROM Books b
INNER JOIN Persons p ON b.owner = p.userCode;

结果校验 #

更新完成后执行以下语句校验数据正确性：

-- 校验是否存在未匹配到有效uid的owner，返回0代表所有数据都更新正确
SELECT COUNT(*) FROM Books WHERE owner NOT IN (SELECT uid FROM Persons);

如果校验通过，后续可以正常给Books表的owner字段添加关联Persons表uid的外键约束即可。

如果你的数据量后续增长到十万甚至百万级别，可以加限制条件分批更新避免长事务锁表，当前1万条的量级不需要额外处理。

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