Postgresql updating query based on join-CodePudding

I am trying to update a table I created on a simple join. I have tried this many different ways and am still getting errors. Any help is appericated

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: syntax error at or near "on" LINE 6: on well.formation_assignments.api14=well.geo.api14; ^ SQL state: 42601 Character: 123

CodePudding user response：

Try this instead :

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

As standard Postgresql update syntax is as follows :

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

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