如何批量将数据表中关联临时ID的字段值替换为对应正式ID

首先明确核心映射规则：所有temp_id非空的记录中，temp_id为待替换的临时ID，同条记录的id为对应的正式ID，我们只需基于这个规则做匹配更新即可。 #

1. 先验证映射关系 #

执行以下查询确认映射规则符合你的业务预期，避免更新错误：

SELECT temp_id AS 临时ID, id AS 对应正式ID
FROM 你的表名
WHERE temp_id IS NOT NULL;

按你给出的示例，这条查询会返回(1,2)、(6,7)，和预期替换逻辑一致。 #

2. 执行更新操作 #

通用SQL方案（兼容所有支持标准SQL的数据库） #

UPDATE 你的表名 t1
SET id = (
    SELECT t2.id
    FROM 你的表名 t2
    WHERE t2.temp_id = t1.id
)
WHERE EXISTS (
    SELECT 1
    FROM 你的表名 t2
    WHERE t2.temp_id = t1.id
);

该语句只会更新存在对应正式ID的临时ID记录，不会改动其他数据，完全匹配你的需求。

MySQL专属优化方案（性能更高，适合千条级数据） #

UPDATE 你的表名 t1
INNER JOIN 你的表名 t2 ON t1.id = t2.temp_id
SET t1.id = t2.id;

PostgreSQL专属优化方案 #

UPDATE 你的表名 t1
SET id = t2.id
FROM 你的表名 t2
WHERE t1.id = t2.temp_id;

3. 注意事项 #

• 执行更新前建议开启事务验证，确认结果正确后再提交：

  BEGIN;
  -- 执行上述更新语句
  UPDATE ...;
  -- 手动查询验证更新结果是否符合预期
  SELECT * FROM 你的表名;
  -- 结果正确执行提交，错误执行回滚即可
  COMMIT; -- 出错则执行 ROLLBACK;
  

• 如果存在多层临时ID映射（比如临时ID1对应临时ID2，临时ID2对应正式ID3），重复执行上述更新语句，直到没有新的行被更新即可。
• 你当前只有数千条待更新数据，上述方案执行效率都足够，不会有性能问题。

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