使用子查询更新 postgres 中的表行

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 方便得多。我相信甲骨文（至少）接受类似的东西。

---

您在使用 UPDATE FROM 语法。

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

参考

此处的代码示例：UPDATE FROM 子句中的 GROUP BY

和这里

正式语法规范

---

如果使用连接没有性能提升，那么我更喜欢通用表表达式 (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 行，您可以使用这些方法中的任何一种。

Approach-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 子句将有助于设置正确的数据映射。

方法 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 子句中使用子查询，并为其赋予别名。这样它就会像桌子一样。

Approach-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 子句中加入多个表。

方法 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 开始，引入了 this(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);

---