How to do an update + join in PostgreSQL?

Question

Basically, I want to do this:

update vehicles_vehicle v      join shipments_shipment s on v.shipment_id=s.id  set v.price=s.price_per_vehicle; 

I'm pretty sure that would work in MySQL (my background), but it doesn't seem to work in postgres. The error I get is:

ERROR:  syntax error at or near "join" LINE 1: update vehicles_vehicle v join shipments_shipment s on v.shi...                                   ^ 

Surely there's an easy way to do this, but I can't find the proper syntax. So, how would I write this In PostgreSQL?

Answer 1

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  

Answer 2

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.