updating table rows in postgres using subquery
postgresql, sql, sql-update, subquery
Solution
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;
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.
Problem
I have this table in a postgres 8.4 database: ``` 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 ); ``` I want to update the table. Initially i tested my query using this `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 ``` How can I convert this to an `update` statement, i.e., update existing rows using values returned from a `select` statement?