如何在PostgreSQL中使用JOIN语句正确更新表？——多表关联更新问题排查与解决

首先，咱们来拆解你原来的UPDATE语句里的核心问题：

• 你写的SET assessment_criteria=employee_assessment.assessment_criteria等于把字段值更新成它自己，这根本不会改变任何数据，这是最关键的错误
• FROM子句里重复关联了employee表，先做了一个子查询t1又和原employee表关联，导致关联逻辑混乱，错误地把不匹配的评估标准（比如AA1-A的XX1X）带到了所有记录上

要实现你的需求——当assessment表更新后，同步更新employee_assessment表，正确的思路应该是：将employee_assessment与employee关联，提取员工类型（employee_id前三位），再关联到最新的assessment表数据，然后把对应的评估标准更新过去。

修正后的SQL语句 #

UPDATE employee_assessment ea
SET assessment_criteria = a.assessment_criteria
FROM employee e
LEFT JOIN assessment a 
  ON LEFT(e.employee_id, 3) = a.employee_type 
  AND e.department = a.department
WHERE ea.employee_id = e.employee_id 
  AND ea.department = e.department;

语句逻辑说明 #

1. 精准关联员工记录：用别名ea指代employee_assessment，e指代employee，通过employee_id和department精准匹配，确保我们更新的是对应员工的评估记录
2. 提取员工类型编码：通过LEFT(e.employee_id, 3)获取员工的类型编码，和assessment表的employee_type做关联
3. 匹配对应部门的评估标准：同时匹配department字段，确保拿到对应部门下的最新评估标准（包括Null值）
4. 同步更新字段：直接把assessment表的最新assessment_criteria赋值给employee_assessment的对应字段，完美同步更新

执行这条语句后，employee_assessment表就会和你期望的结果完全一致：

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