How to Update Postgres Table Rows Using Subqueries?

Updating Table Rows in Postgres Using Subqueries

In Postgres, it is possible to perform updates on table rows by using subqueries to retrieve the desired values for each row. One way to achieve this is through the following syntax:

UPDATE table_name
SET column_name = (SELECT value FROM subquery WHERE subquery_condition)
WHERE table_condition;

Consider the following example, where the goal is to update the dummy table in a Postgres 8.4 database, filling in the customer, supplier, and partner columns with values derived from a subquery:

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
);

UPDATE dummy
SET customer = (SELECT
    CASE WHEN cust.addr1 IS NOT NULL THEN TRUE ELSE FALSE END),
    supplier = (SELECT
    CASE WHEN suppl.addr1 IS NOT NULL THEN TRUE ELSE FALSE END),
    partner = (SELECT
    CASE WHEN partn.addr1 IS NOT NULL THEN TRUE ELSE FALSE END)
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 SUBSTR(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 = SUBSTR(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 = SUBSTR(partn.zip,1,5) )
WHERE pa.address_id = address_id;

This query effectively updates the customer, supplier, and partner columns for each row in the dummy table, setting them to TRUE if the corresponding address is found in the cust_original, supp_original, and partner_original tables, or to FALSE otherwise.

This syntax is not standard SQL but offers convenience for updating table rows based on values derived from subqueries, especially in Postgres.

The above is the detailed content of How to Update Postgres Table Rows Using Subqueries?. For more information, please follow other related articles on the PHP Chinese website!