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

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语句测试我的查询:

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语句返回的值更新现有行。非常感谢您的帮助。

3 个解决方案

#1

421

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

#2

You're after the UPDATE FROM syntax.

您需要从语法进行更新。

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
这里的代码示例:在FROM子句中进行分组更新
And here
这里
Formal Syntax Specification
正式的语法规范

#3

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

如果使用join没有提高性能，那么为了可读性，我更喜欢通用表表达式(Common Table Expressions, 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;

IMHO a bit more modern.

更现代一点。

#1

421