title: updating table rows in postgres using subquery
body: <p>Using postgres 8.4,  My goal is to update existing table:</p>
  
  <pre><code>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
  );
  </code></pre>
  
  <p>Initially i tested my query using insert statement:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>being Newbie I'm failing converting to update statement ie., updating existing rows with values returned by select statement.
  Any help is highly appreciated.</p>