How to do an update + join in PostgreSQL?

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 table1
set value = t.calculatedvalue
from t
where 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_vehicle
set price = t.calculatedvalue
from t
where id = t.rowid

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


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_vehicle
FROM shipments_shipment AS s
WHERE v.shipment_id = s.id 

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 a
JOIN (
    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
) b
ON a.id = b.id
SET a.documents_taken_at = b.certificate_issued_at;

Becomes PostgreSQL-like in such a way

UPDATE applications a
SET documents_taken_at = b.certificate_issued_at         -- we can reference joined table here
FROM abiturients b                                       -- joined table
WHERE 
    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).