——目标:使用join (postgres)更新选定的列——

UPDATE table1 t1      
SET    column1 = 'data' 
FROM   table1    
       RIGHT JOIN table2   
               ON table2.id = table1.id   
WHERE  t1.id IN     
(SELECT table2.id   FROM   table2   WHERE  table2.column2 = 12345) 

下面的链接提供了一个示例，可以帮助您更好地理解如何使用update和join postgres。

UPDATE product
SET net_price = price - price * discount
FROM
product_segment
WHERE
product.segment_id = product_segment.id;

参见:http://www.postgresqltutorial.com/postgresql-update-join/

开始吧:

update vehicles_vehicle v
set price=s.price_per_vehicle
from shipments_shipment s
where v.shipment_id=s.id;

我能做到的最简单。

对于那些真正想要做JOIN的人，你也可以使用:

UPDATE a
SET price = b_alias.unit_price
FROM      a AS a_alias
LEFT JOIN b AS b_alias ON a_alias.b_fk = b_alias.id
WHERE a_alias.unit_name LIKE 'some_value' 
AND a.id = a_alias.id;

如果需要，可以在等号右侧的SET部分中使用a_alias。 等号左边的字段不需要表引用，因为它们被认为来自原始的“a”表。

对于那些想要做一个JOIN，更新你的连接返回行使用:

UPDATE a
SET price = b_alias.unit_price
FROM      a AS a_alias
LEFT JOIN b AS b_alias ON a_alias.b_fk = b_alias.id
WHERE a_alias.unit_name LIKE 'some_value' 
AND a.id = a_alias.id
--the below line is critical for updating ONLY joined rows
AND a.pk_id = a_alias.pk_id;

这是上面提到的，但只是通过一个评论..因为它是至关重要的，以获得正确的结果张贴新的答案，工作

在这种情况下，Mark Byers的答案是最优的。 尽管在更复杂的情况下，你可以使用select查询返回rowids和计算值，并将其附加到更新查询，如下所示:

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

这种方法允许您开发和测试选择查询，并在两个步骤中将其转换为更新查询。

所以在你的例子中，结果查询将是:

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

请注意，列别名是必须的，否则PostgreSQL将抱怨列名的模糊性。