How to do an update + join in PostgreSQL?

The UPDATE syntax is:

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

In your case I think you want this:

UPDATE vehicles_vehicle AS v SET price = s.price_per_vehicleFROM shipments_shipment AS sWHERE v.shipment_id = s.id 

The answer of Mark Byers is the optimal in this situation. Though in more complex situations you can take the select query that returns rowids and calculated values and attach it to the update query like this:

with t as (  -- Any generic query which returns rowid and corresponding calculated values  select t1.id as rowid, f(t2, t2) as calculatedvalue  from table1 as t1  join table2 as t2 on t2.referenceid = t1.id)update table1set value = t.calculatedvaluefrom twhere id = t.rowid

This approach lets you develop and test your select query and in two steps convert it to the update query.

So in your case the result query will be:

with t as (    select v.id as rowid, s.price_per_vehicle as calculatedvalue    from vehicles_vehicle v     join shipments_shipment s on v.shipment_id = s.id )update vehicles_vehicleset price = t.calculatedvaluefrom twhere id = t.rowid

Note that column aliases are mandatory otherwise PostgreSQL will complain about the ambiguity of the column names.

Let me explain a little more by my example.

Task: correct info, where abiturients (students about to leave secondary school) have submitted applications to university earlier, than they got school certificates (yes, they got certificates earlier, than they were issued (by certificate date specified). So, we will increase application submit date to fit certificate issue date.

Thus. next MySQL-like statement:

UPDATE applications aJOIN (    SELECT ap.id, ab.certificate_issued_at    FROM abiturients ab    JOIN applications ap     ON ab.id = ap.abiturient_id     WHERE ap.documents_taken_at::date < ab.certificate_issued_at) bON a.id = b.idSET a.documents_taken_at = b.certificate_issued_at;

Becomes PostgreSQL-like in such a way

UPDATE applications aSET documents_taken_at = b.certificate_issued_at         -- we can reference joined table hereFROM abiturients b                                       -- joined tableWHERE     a.abiturient_id = b.id AND                           -- JOIN ON clause    a.documents_taken_at::date < b.certificate_issued_at -- Subquery WHERE

As you can see, original subquery JOIN's ON clause have become one of WHERE conditions, which is conjucted by AND with others, which have been moved from subquery with no changes. And there is no more need to JOIN table with itself (as it was in subquery).