Postgresql基于join更新查询

Question

我正在尝试更新我在一个简单连接上创建的表。我已经尝试了许多不同的方法，仍然得到错误。感谢您的帮助

Update well.formation_assignments as v
set
Primary_Formation = well.geo.primary_formation
from well.geo as s
inner join 
on well.formation_assignments.api14=well.geo.api14;

ERROR: "on"第6行:on well.formation_assignments.api14=well.geo.api14;^SQL状态:42601性格:123

Answer 1

试试这个:

UPDATE well.formation_assignments v
SET Primary_Formation = s.primary_formation
FROM well.geo s
WHERE v.api14 = s.api14;

作为标准Postgresql的更新语法如下:

[ 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 ] [, ...] ]

文档:https://www.postgresql.org/docs/9.1/sql-update.html