How to do an update + join in PostgreSQL?

postgresql

Solution

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

Or if you need to join on two or more tables:

UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
    JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
    t2.id = t1.t2_id
    AND t3.bar = True;

Problem

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?

Original source