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

updating table rows in postgres using subquery

提问by stackover

Using postgres 8.4, My goal is to update existing table:

使用 postgres 8.4，我的目标是更新现有表：

CREATE TABLE public.dummy
(
  address_id SERIAL,
  addr1 character(40),
  addr2 character(40),
  city character(25),
  state character(2),
  zip character(5),
  customer boolean,
  supplier boolean,
  partner boolean

)
WITH (
  OIDS=FALSE
);

Initially i tested my query using insert statement:

最初我使用插入语句测试了我的查询：

insert into address customer,supplier,partner
SELECT  
    case when cust.addr1 is not null then TRUE else FALSE end customer, 
    case when suppl.addr1 is not null then TRUE else FALSE end supplier,
    case when partn.addr1 is not null then TRUE else FALSE end partner
from (
    SELECT *
        from address) pa
    left outer join cust_original cust
        on (pa.addr1=cust.addr1 and pa.addr2=cust.addr2 and pa.city=cust.city 
            and pa.state=cust.state and substring(cust.zip,1,5) = pa.zip  )
    left outer join supp_original suppl 
        on (pa.addr1=suppl.addr1 and pa.addr2=suppl.addr2 and pa.city=suppl.city 
                and pa.state=suppl.state and pa.zip = substring(suppl.zip,1,5))
    left outer join partner_original partn
        on (pa.addr1=partn.addr1 and pa.addr2=partn.addr2 and pa.city=partn.city
                  and pa.state=partn.state and pa.zip = substring(partn.zip,1,5) )
where pa.address_id = address_id

being Newbie I'm failing converting to update statement ie., updating existing rows with values returned by select statement. Any help is highly appreciated.

作为新手，我无法转换为更新语句，即使用 select 语句返回的值更新现有行。任何帮助都受到高度赞赏。

回答by Andrew Lazarus

Postgres allows:

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;

This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.

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

回答by Brian Webster

You're after the UPDATE FROMsyntax.

你在追求UPDATE FROM语法。

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

References

参考

• Code sample here: GROUP BY in UPDATE FROM clause
• And here
• Formal Syntax Specification

• 此处的代码示例：UPDATE FROM 子句中的 GROUP BY
• 和这里
• 正式语法规范

回答by steevee

If there are no performance gains using a join, then I prefer Common Table Expressions (CTEs) for readability:

如果使用连接没有性能提升，那么我更喜欢通用表表达式 (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;

IMHO a bit more modern.

恕我直言，更现代一点。

回答by Mayur

There are many ways to update the rows.

有很多方法可以更新行。

When it comes to UPDATEthe rows using subqueries, you can use any of these approaches.

当涉及UPDATE使用子查询的行时，您可以使用这些方法中的任何一种。

1. Approach-1 [Using direct table reference]

1. 方法 1 [使用直接表引用]

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

Explanation: table1is the table which we want to update, table2is the table, from which we'll get the value to be replaced/updated. We are using FROMclause, to fetch the table2's data. WHEREclause will help to set the proper data mapping.

说明：table1是我们要更新table2的表，是我们将要从中获取要替换/更新的值的表。我们正在使用FROM子句来获取table2的数据。WHERE子句将有助于设置正确的数据映射。

1. Approach-2 [Using SubQueries]

2. 方法 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;

Explanation: Here we are using subquerie inside the FROMclause, and giving an alias to it. So that it will act like the table.

说明：这里我们在FROM子句中使用子查询，并给它一个别名。这样它就会像桌子一样工作。

1. Approach-3 [Using multiple Joined tables]

3. 方法 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;

Explanation: Sometimes we face the situation in that table join is so important to get proper data for the update. To do so, Postgres allows us to Join multiple tables inside the FROMclause.

解释：有时我们会遇到这样的情况，表连接对于获取正确的更新数据非常重要。为此，Postgres 允许我们在FROM子句中加入多个表。

1. Approach-4 [Using WITH statement]

   • 4.1 [Using simple query]

4. 方法 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 [Using query with complex JOIN]

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

Explanation: From Postgres 9.1, this(WITH) concept has been introduces. Using that We can make any complex queries and generate desire result. Here we are using this approach to update the table.

说明：从 Postgres 9.1 开始，WITH引入了this( ) 的概念。使用它我们可以进行任何复杂的查询并生成所需的结果。这里我们使用这种方法来更新表。

I hope, this would be helpful.

我希望，这会有所帮助。

回答by Pugazendhi Asaimuthu

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;

回答by Festus Ngor

@Mayur "4.2 [Using query with complex JOIN]"with Common Table Expressions (CTEs)did the trick for me.

@Mayur "4.2 [Using query with complex JOIN]"和Common Table Expressions (CTEs)对我有用。

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;

Hope this helps... :D

希望这会有所帮助... :D