基本上,我想这样做:

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

我很确定这在MySQL(我的背景)中可以工作,但在postgres中似乎不起作用。我得到的错误是:

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

当然有一个简单的方法来做到这一点,但我找不到合适的语法。那么,我该如何在PostgreSQL中写这个呢?


当前回答

UPDATE语法为:

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

在你的情况下,我认为你想要的是:

UPDATE vehicles_vehicle AS v 
SET price = s.price_per_vehicle
FROM shipments_shipment AS s
WHERE v.shipment_id = s.id 

或者如果你需要连接两个或多个表:

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;

其他回答

对于那些想要做一个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;

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

对于那些真正想要做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的POSTGRE SQL - UPDATE

下面代码-检查列和id的定位如下:

如果你把它放在下面,那么只有它会工作!

---IF you want to update FIRST table
UPDATE table1
SET attribute1 = table2.attribute1
FROM table2
WHERE table2.product_ID = table1.product_ID;

OR

---IF you want to update SECOND table
UPDATE table2
SET attribute1 = table1.attribute1
FROM table1
WHERE table1.product_ID = table2.product_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将抱怨列名的模糊性。

在上面所有的答案中添加一些非常重要的东西,当你想要更新连接表时,你可能会遇到两个问题:

您不能使用您想要更新的表来JOIN另一个表 Postgres希望在JOIN之后有一个ON子句,因此不能只使用where子句。

这意味着基本上,以下查询是无效的:

UPDATE join_a_b
SET count = 10
FROM a
JOIN b on b.id = join_a_b.b_id -- Not valid since join_a_b is used here
WHERE a.id = join_a_b.a_id
AND a.name = 'A'
AND b.name = 'B'
UPDATE join_a_b
SET count = 10
FROM a
JOIN b -- Not valid since there is no ON clause
WHERE a.id = join_a_b.a_id 
AND b.id = join_a_b.b_id
a.name = 'A'
AND b.name = 'B'

相反,你必须像这样使用FROM子句中的所有表:

UPDATE join_a_b
SET count = 10
FROM a, b
WHERE a.id = join_a_b.a_id 
AND b.id = join_a_b.b_id 
AND a.name = 'A'
AND b.name = 'B'

这对一些人来说可能很简单,但我被这个问题困住了,想知道发生了什么,所以希望它能帮助到其他人。