postgresql的update inner join

在mysql中遇到依赖表a的数据来大量更新表b的数据时可以使用update join的语法
在postgresql也可以做到 语法和mysql有一些差别

这是postgresql的update语法

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

update中可以包含一个form子句 当包含form子句时 where子句中需要指明update的表和form子句的关联关系
例如：
employee:id name attendance_duration ...
attendance: employee_id duration date ...
将全部员工上月的出勤时间累加到employee表的attendance_duration中
update employee set attendance_duration=attendance_duration+t.ad
from (select sum(duration) from attendance where date<... and date >... group by employee_id) as t
where employee.id =attendance.employee_id

利用这个特性配合case when等语法实现复杂业务逻辑 可以避免大量数据逐一更新
能极大提高update性能