Postgres允许：

UPDATE dummy
SET customer=subquery.customer,
    address=subquery.address,
    partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
      FROM  /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;

这种语法不是标准SQL，但对于这种类型的查询，它比标准SQL方便得多。我相信Oracle（至少）接受类似的东西。

您正在使用UPDATE FROM语法。

UPDATE 
  table T1  
SET 
  column1 = T2.column1 
FROM 
  table T2 
  INNER JOIN table T3 USING (column2) 
WHERE 
  T1.column2 = T2.column2;

• • 参考资料**
• 代码示例如下：GROUP BY in UPDATE FROM clause
• And here
• Formal Syntax Specification

如果使用连接没有提高性能，那么我更喜欢通用表表达式（Common Table Expressions，CTE）的可读性：

WITH subquery AS (
    SELECT address_id, customer, address, partn
    FROM  /* big hairy SQL */ ...
)
UPDATE dummy
SET customer = subquery.customer,
    address  = subquery.address,
    partn    = subquery.partn
FROM subquery
WHERE dummy.address_id = subquery.address_id;

恕我直言，更现代一点。

有许多方法可以更新行。
当使用子查询对行进行UPDATE时，可以使用以下任何一种方法。
1.方法-1 [使用直接表引用]

UPDATE
  <table1>
SET
  customer=<table2>.customer,
  address=<table2>.address,
  partn=<table2>.partn
FROM
  <table2>
WHERE
  <table1>.address_id=<table2>.address_i;

说明：table1是要更新的表，table2是从中获取要替换/更新的值的表。我们使用FROM子句来获取table2的数据。WHERE子句将帮助设置正确的数据Map。
1.方法-2 [使用子查询]

UPDATE
  <table1>
SET
  customer=subquery.customer,
  address=subquery.address,
  partn=subquery.partn
FROM
  (
    SELECT
      address_id, customer, address, partn
    FROM  /* big hairy SQL */ ...
  ) AS subquery
WHERE
  dummy.address_id=subquery.address_id;

说明：这里我们在FROM子句中使用subquerie，并给它一个别名，这样它就像表一样工作。
1.方法-3 [使用多个联接表]

UPDATE
  <table1>
SET
  customer=<table2>.customer,
  address=<table2>.address,
  partn=<table2>.partn
FROM
  <table2> as t2
  JOIN <table3> as t3
  ON
    t2.id = t3.id
WHERE
  <table1>.address_id=<table2>.address_i;

说明：有时候我们会遇到这样的情况，表连接对于获取正确的数据是非常重要的，为了做到这一点，Postgres允许我们在FROM子句中连接多个表。
1.方法-4 [使用WITH语句]

• 4.1 [使用简单查询]

WITH subquery AS (
    SELECT
      address_id,
      customer,
      address,
      partn
    FROM
      <table1>;
)
UPDATE <table-X>
SET customer = subquery.customer,
    address  = subquery.address,
    partn    = subquery.partn
FROM subquery
WHERE <table-X>.address_id = subquery.address_id;

• 4.2 [使用具有复杂JOIN的查询]

WITH subquery AS (
    SELECT address_id, customer, address, partn
    FROM
      <table1> as t1
    JOIN
      <table2> as t2
    ON
      t1.id = t2.id;
    -- You can build as COMPLEX as this query as per your need.
)
UPDATE <table-X>
SET customer = subquery.customer,
    address  = subquery.address,
    partn    = subquery.partn
FROM subquery
WHERE <table-X>.address_id = subquery.address_id;

说明：从Postgres 9.1开始，我们引入了（WITH）的概念。使用这个概念，我们可以进行任何复杂的查询并生成所需的结果。这里我们使用这种方法来更新表。
我希望，这会有帮助。😊

@Mayur***“4.2 [使用带有复杂JOIN的查询]"***使用***通用表表达式（CTE）***对我来说很有用。

WITH cte AS (
SELECT e.id, e.postcode
FROM employees e
LEFT JOIN locations lc ON lc.postcode=cte.postcode
WHERE e.id=1
)
UPDATE employee_location SET lat=lc.lat, longitude=lc.longi
FROM cte
WHERE employee_location.id=cte.id;

希望这对你有帮助...：D

update json_source_tabcol as d
set isnullable = a.is_Nullable
from information_schema.columns as a 
where a.table_name =d.table_name 
and a.table_schema = d.table_schema 
and a.column_name = d.column_name;

对于PostgreSQL，请检查https://www.postgresql.org/docs/current/sql-update.html

UPDATE tableA SET (addr1, adrr2) =
    (SELECT addr1, addr2 FROM tableB
     WHERE tableA.id = tableB.tableA_id);