对于那些真正想要做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”表。

第一种方式比第二种方式慢。

第一:

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 / 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秒

在这种情况下，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将抱怨列名的模糊性。

下面的链接提供了一个示例，可以帮助您更好地理解如何使用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/

我举个例子再解释一下。

任务:正确的信息，在哪里abiturients(即将离开中学的学生)提交申请大学早于他们获得学校证书(是的，他们获得证书早于他们颁发的证书(指定证书日期)。因此，我们将增加申请提交日期，以适应证书颁发日期。

因此。下一个mysql类语句:

UPDATE applications a
JOIN (
    SELECT ap.id, ab.certificate_issued_at
    FROM abiturients ab
    JOIN applications ap 
    ON ab.id = ap.abiturient_id 
    WHERE ap.documents_taken_at::date < ab.certificate_issued_at
) b
ON a.id = b.id
SET a.documents_taken_at = b.certificate_issued_at;

以这样的方式变得像postgresql

UPDATE applications a
SET documents_taken_at = b.certificate_issued_at         -- we can reference joined table here
FROM abiturients b                                       -- joined table
WHERE 
    a.abiturient_id = b.id AND                           -- JOIN ON clause
    a.documents_taken_at::date < b.certificate_issued_at -- Subquery WHERE

可以看到，原来的子查询JOIN的ON子句已经变成了WHERE条件之一，由AND与其他子查询连接，这些子查询已经从子查询中移动，没有任何变化。并且不再需要将表与自身连接(就像在子查询中那样)。