如何复制表列的DISTINCT数据集到新表并将新数据ID回写至原表

通用基础方案（适配绝大多数主流数据库） #

只要你用的是支持JOIN更新语法的数据库（MySQL、PostgreSQL、SQL Server均兼容），直接通过两表的Item_Name字段关联匹配更新即可，这是最通用、易维护的方案，操作前请先在Table1中新增Item_Name_ID字段。
MySQL语法示例：

UPDATE Table1 t1
INNER JOIN Table2 t2 
ON t1.Item_Name = t2.Item_Name
SET t1.Item_Name_ID = t2.Item_Name_ID;

注意：操作前务必提前备份全量数据，避免关联逻辑错误导致数据损坏 #

大数据量场景优化方案 #

如果Table1数据量超过10万条，单次全表更新可能锁表时间过长或执行效率低，可以做两个优化：

• 提前给Table1的Item_Name字段、Table2的Item_Name字段添加普通索引，关联匹配速度可提升数倍，更新完成后如果不需要保留索引可以删除
• 分批执行更新，按ID范围拆分任务降低锁表影响，示例：

-- 每批次更新1000条，可根据数据库性能调整批次大小
UPDATE Table1 t1
INNER JOIN Table2 t2 
ON t1.Item_Name = t2.Item_Name
SET t1.Item_Name_ID = t2.Item_Name_ID
WHERE t1.ID BETWEEN 1 AND 1000;
-- 依次调整ID区间，直到全表更新完成

性能最优方案（适合还未执行Table2插入的场景） #

如果你还没有完成Table2的去重值插入，可以将插入和回写合并成一个事务执行，减少一次全表扫描，性能比先插后更高，PostgreSQL语法示例（用CTE+RETURNING实现）：

WITH inserted_items AS (
    INSERT INTO Table2 (Item_Name)
    SELECT DISTINCT Item_Name FROM Table1
    RETURNING Item_Name_ID, Item_Name
)
UPDATE Table1 t1
SET Item_Name_ID = inserted_items.Item_Name_ID
FROM inserted_items
WHERE t1.Item_Name = inserted_items.Item_Name;

SQL Server可以用OUTPUT子句实现相同逻辑。 #

结果校验 #

更新完成后执行以下SQL确认关联正确性：

SELECT COUNT(*) FROM Table1 WHERE Item_Name_ID IS NULL;

返回结果为0即代表所有行都关联成功，之后可删除Table1的Item_Name字段完成最终结构调整。

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