基本上，我想要做到这个：
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中，我该怎么写呢？
使用另一张表来更新一张表，在PostGRE SQL / AWS（SQL workbench）中。
在PostGRE SQL中，这是您需要在UPDATE查询中使用连接的方法：
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 - 员工表，包含以下列 - 离职日期、员工ID、离职标志。表B - 员工离职清算表，包含以下列 - 记录的离职日期、离职员工ID。
共影响了1760行数据。
执行时间：29.18秒。
如果在非唯一列上执行连接操作，则应该小心。即，连接的结果会产生更多的值，这些值可以用于更新。
某些关系型数据库管理系统在这种情况下会引发异常，但是PostgreSQL显然会使用非确定性结果进行更新。
示例
在14.1上测试过
create table tab as
select * from (values
(1,'a'),
(2,'b')
) t(id, att);
我们使用CTE，其中id = 1提供了两个可能的更新值。在CTE中使用order by，我们可以得到不同的结果。
with t as (
select * from (values
(1,'c'),
(1,'d')
) t(id, att)
order by 2 /* Based on this order different update is performed */
)
update tab
set att = t.att
from t
where tab.id = t.id
使用升序，该列将被更新为d的值（最高值）
id|att|
--+---+
1|d |
2|b |
在使用CTE的降序时，该列会更新为c（最低值）的值
id|att|
--+---+
1|c |
2|b |
故事的寓意是要始终检查连接是否产生了唯一的结果。
相关文档的重要部分
使用FROM时，应确保每个要修改的行最多只产生一个输出行。换句话说，目标行不应与其他表中的多行连接。如果确实如此，则仅使用其中一行连接行来更新目标行，但将使用哪一行并不容易预测。
--目标：使用连接（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)
第一种方法比第二种方法慢。
首先：
DO $$
DECLARE
page int := 10000;
min_id bigint; max_id bigint;
BEGIN
SELECT max(id),min(id) INTO max_id,min_id FROM opportunities;
FOR j IN min_id..max_id BY page LOOP
UPDATE opportunities SET sec_type = 'Unsec'
FROM opportunities AS opp
INNER JOIN accounts AS acc
ON opp.account_id = acc.id
WHERE acc.borrower = true
AND opp.sec_type IS NULL
AND opp.id >= j AND opp.id < j+page;
COMMIT;
END LOOP;
END; $$;
第二点：
DO $$
DECLARE
page int := 10000;
min_id bigint; max_id bigint;
BEGIN
SELECT max(id),min(id) INTO max_id,min_id FROM opportunities;
FOR j IN min_id..max_id BY page LOOP
UPDATE opportunities AS opp
SET sec_type = 'Unsec'
FROM accounts AS acc
WHERE opp.account_id = acc.id
AND opp.sec_type IS NULL
AND acc.borrower = true
AND opp.id >= j AND opp.id < j+page;
COMMIT;
END LOOP;
END; $$;
工作完美!!!
POSTGRE SQL - 使用JOIN进行更新
以下代码 - 请检查列和ID的位置如下：
如果您将其放置在完全如下的位置，则它才能正常工作！
---IF you want to update This table1 using table2
UPDATE table1
SET attribute1 = table2.attribute2
FROM table2
WHERE table2.product_ID = table1.product_ID;
shipments_shipment计算price_per_vehicle），那么假设shipments_shipment具有price和vehicle_id列，则特定车辆的更新可能如下所示：-- Specific vehicle in this example is with id = 5
WITH prices AS (
SELECT SUM(COALESCE(s.price, 0)) AS price_per_vehicle
FROM shipments_shipment AS s
WHERE s.vehicle_id = 5
)
UPDATE vehicles_vehicle AS v
SET v.price = prices.price_per_vehicle
FROM prices
WHERE v.id = 5
很遗憾运行时间如此糟糕，因为语法非常优雅。 我保留这个答案，以免其他人走上这条路。
结果是，这适用于自然连接，非常方便。
假设你有一个名为shipment的表，你想要从vehicle表中获取信息，并且两个表都有一个名为vehicle_id的列，所以你可以使用NATURAL JOIN。
---- DO NOT USE, quadratic runtime ----
EXPLAIN UPDATE shipment
SET shipment.speed = vehicle.average_speed
FROM
shipment s_joined NATURAL JOIN vehicle
WHERE
-- This is the magic condition
-- (EDIT: ... it probably causes the quadratic runtime, too)
shipment = s_joined
-- any further limitations go here:
AND shipment.destination = 'Perth'
-- A table with shipments, some with missing speeds
create temporary table shipment (
vehicle_id varchar(20),
cargo varchar(20),
speed integer
);
insert into shipment values
('cart', 'flowers', 60),
('boat', 'cabbage', null),
('cart', 'potatos', null),
('foot', 'carrots', null);
-- A table with vehicles whose average speed we know about
create temporary table vehicle (
vehicle_id varchar(20),
average_speed integer
);
insert into vehicle values
('cart', 6),
('foot', 5);
-- If the shipment has vehicle info, update its speed
---- DO NOT USE, quadratic runtime ----
UPDATE shipment
SET speed = vehicle.average_speed
FROM shipment as s_joined natural join vehicle
WHERE shipment = s_joined
AND shipment.speed is null;
-- After:
TABLE shipment;
┌────────────┬─────────┬───────┐
│ vehicle_id │ cargo │ speed │
├────────────┼─────────┼───────┤
│ cart │ flowers │ 60 │ <- not updated: speed was not null
│ boat │ cabbage │ │ <- not updated: no boat in join
│ cart │ potatos │ 6 │ <- updated
│ foot │ carrots │ 5 │ <- updated
└────────────┴─────────┴───────┘
vehicles_*的视图表和几个shipments_*表。 - mpen