开始吧:

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

我能做到的最简单。

——目标:使用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) 

我举个例子再解释一下。

任务:正确的信息，在哪里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与其他子查询连接，这些子查询已经从子查询中移动，没有任何变化。并且不再需要将表与自身连接(就像在子查询中那样)。

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

第一:

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; $$;

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

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

您不能使用您想要更新的表来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'

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