postgresql - 基于join的PostgreSQL更新查询

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;

错误：第 6 行或“on”附近的语法错误：on well.formation_assignments.api14=well.geo.api14; ^ SQL 状态：42601 字符：123

标签： postgresqlsql-update

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