在postgresql 16中,可以通过在 update 语句的 from 子句中指定关联表来实现基于其他表数据的更新。具体做法是编写包含 update target_table from source_table 的sql语句,并在 where 子句中定义两表的连接条件,这样可以将源表中的值直接赋值给目标表的列。

如何基于单表数据更新另一张表?
当需要根据另一张独立表的数据来更新目标表时,只需在 FROM 子句中列出源表并建立连接条件即可。例如,若要根据 departments 表中的部门名称更新 employees 表中的部门信息,可以使用以下语法。这种写法避免了子查询的性能开销,且逻辑清晰。

UPDATE employees e SET department_name = d.name FROM departments d WHERE e.department_id = d.id;
执行上述语句后,employees 表中所有匹配的记录都会更新其 department_name 字段。如果 departments 中存在多个匹配项(虽然主键通常保证唯一),PostgreSQL 会随机选择其中一个值进行更新,因此建议确保连接条件的唯一性。预期结果是 employees 表中的部门名称与 departments 表保持一致。
如何处理多表关联更新?
如果需要基于三张或更多表的关联数据进行更新,可以在 FROM 子句中列出所有涉及的表,并在 WHERE 子句中定义它们之间的连接关系。例如,若要同时根据部门和地区信息更新员工薪资等级,可以扩展上述查询。这种方法允许在一次操作中综合多个来源的数据。

UPDATE employees e SET salary_grade = sg.grade FROM departments d, regions r, salary_grades sg WHERE e.department_id = d.id AND d.region_id = r.id AND r.country_code = sg.country_code AND e.salary BETWEEN sg.min_salary AND sg.max_salary;
在此示例中,employees 通过 departments 和 regions 关联到 salary_grades。只有当所有连接条件满足时,更新才会发生。如果任何一张表中没有匹配记录,该行将不会被更新。预期结果是 employees 表中的 salary_grade 字段根据员工的薪资范围和所属国家被正确填充。
当FROM子句中的源表存在重复键时会发生什么?
如果在 FROM 子句中指定的源表存在重复键,导致目标表的一行对应源表的多行,PostgreSQL 的行为是不确定的。它会从匹配的行中随机选择一行进行更新,而不是报错或合并数据。这意味着结果可能每次执行都不一致,除非源表具有唯一约束或连接条件能确保一对一映射。为了避免这种情况,应确保 FROM 子句中的表在连接条件下对目标表的每一行只返回一条记录。










