stackover stackover - 1 year ago 85
SQL Question

updating table rows in postgres using subquery

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


Initially i tested my query using insert statement:

insert into address customer,supplier,partner
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 (
from address) pa
left outer join cust_original cust
on (pa.addr1=cust.addr1 and pa.addr2=cust.addr2 and
and pa.state=cust.state and substring(,1,5) = )
left outer join supp_original suppl
on (pa.addr1=suppl.addr1 and pa.addr2=suppl.addr2 and
and pa.state=suppl.state and = substring(,1,5))
left outer join partner_original partn
on (pa.addr1=partn.addr1 and pa.addr2=partn.addr2 and
and pa.state=partn.state and = substring(,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.

Answer Source

Postgres allows:

UPDATE dummy
SET customer=subquery.customer,
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download