使用子查询更新 postgres 中的表行 [英] updating table rows in postgres using subquery

问题描述

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

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

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

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

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

推荐答案

Postgres 允许:

Postgres allows:

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(至少)接受类似的东西.

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.

这篇关于使用子查询更新 postgres 中的表行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！