基本上，我想这样做:
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 (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)
在PostGRE SQL / AWS (SQL工作台)中使用另一个表更新一个表。
在PostGRE SQL中，你需要在UPDATE Query中使用join:
UPDATE TABLEA set COLUMN_FROM_TABLEA = COLUMN_FROM_TABLEB FROM TABLEA,TABLEB WHERE FILTER_FROM_TABLEA = FILTER_FROM_TABLEB;
Example:
Update Employees Set Date_Of_Exit = Exit_Date_Recorded , Exit_Flg = 1 From Employees, Employee_Exit_Clearance Where Emp_ID = Exit_Emp_ID
表A - Employees列表A - Date_Of_Exit,Emp_ID,Exit_Flg表B是- Employee_Exit_Clearance列表B - Exit_Date_Recorded,Exit_Emp_ID
1760行受影响
执行时间:29.18秒
完美的工作! !
带有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的人，你也可以使用:
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”表。
