updating table rows in postgres using subquery

Question

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

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 1

Postgres allows the below query:

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;

Note: But the above syntax is not the standard SQL, so this is convenient for this type of query. This is also valid in 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.