将DEPARTMENTS表ID_DEPARTMENT数据复制到DEP表ID字段报错或无更新如何解决

常见报错/无更新原因 #

• 缺失两表关联条件：未指定匹配规则直接更新，要么触发笛卡尔积导致主键重复报错，要么无匹配行导致0行更新
• 字段约束冲突：DEP表ID列为主键/唯一键，DEPARTMENTS表的ID_DEPARTMENT存在重复值；或是ID列关联了外键，待写入的ID_DEPARTMENT值不存在于外键关联表中触发报错
• 关联条件不匹配：关联字段类型不一致（如INT类型和带空格的VARCHAR类型）、字段值对应不上，导致没有命中可更新的行

操作前置要求 #

操作前务必先备份DEP表全量数据，避免误操作导致数据丢失
更新前先执行关联查询验证匹配结果，确认待更新的值符合预期：

-- 示例关联字段为两表共有的部门名称DEPARTMENT_NAME，可替换为实际关联字段
SELECT d.ID AS 原DEP表ID, dept.ID_DEPARTMENT AS 待同步ID, d.DEPARTMENT_NAME AS 关联部门名
FROM DEP d
JOIN DEPARTMENTS dept ON d.DEPARTMENT_NAME = dept.DEPARTMENT_NAME;

如果上述查询返回行数为0，需调整关联字段后再执行后续更新操作。

注意：禁止无关联条件直接更新，否则会出现数据错配、约束冲突等问题。

各数据库正确更新语法 #

MySQL #

UPDATE DEP d
INNER JOIN DEPARTMENTS dept ON d.DEPARTMENT_NAME = dept.DEPARTMENT_NAME
SET d.ID = dept.ID_DEPARTMENT;

PostgreSQL #

UPDATE DEP d
SET ID = dept.ID_DEPARTMENT
FROM DEPARTMENTS dept
WHERE d.DEPARTMENT_NAME = dept.DEPARTMENT_NAME;

Oracle #

UPDATE DEP d
SET d.ID = (SELECT dept.ID_DEPARTMENT FROM DEPARTMENTS dept WHERE d.DEPARTMENT_NAME = dept.DEPARTMENT_NAME)
WHERE EXISTS (SELECT 1 FROM DEPARTMENTS dept WHERE d.DEPARTMENT_NAME = dept.DEPARTMENT_NAME);

SQL Server #

UPDATE d
SET d.ID = dept.ID_DEPARTMENT
FROM DEP d
INNER JOIN DEPARTMENTS dept ON d.DEPARTMENT_NAME = dept.DEPARTMENT_NAME;

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