使用subquery更新postgres中的表行

我在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
);

我想更新表格。最初，我测试我的查询使用这个插入语句:

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

如何将其转换为更新语句，即使用从选择语句返回的值更新现有行?

当前回答

如果使用连接没有性能提升，那么我更喜欢通用表表达式(CTEs)的可读性:

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;

以我之见，更现代一点。

2017-08-02 15:56:23

其他回答

您在使用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 这里形式语法规范

2011-06-06 20:30:23

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;

2018-09-15 04:29:00

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

2011-06-06 22:07:30

如果使用连接没有性能提升，那么我更喜欢通用表表达式(CTEs)的可读性:

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;

以我之见，更现代一点。

2017-08-02 15:56:23

对于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);

2022-06-01 19:49:34

使用subquery更新postgres中的表行

推荐文章

最新文章

标签