How to Update PostgreSQL Rows with Values from a Subquery?

Updating PostgreSQL Table Rows Using Subqueries

To update existing rows in a PostgreSQL table using values returned from a subquery, you can utilize the following syntax:

UPDATE table_name
SET column_name = subquery.column_name
FROM (SELECT ...) AS subquery
WHERE table_name.id = subquery.id;

Consider the example table dummy you provided:

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

To update the customer, supplier, and partner columns based on values returned from a select statement, you can use the following syntax:

UPDATE dummy
SET customer = subquery.customer,
    supplier = subquery.supplier,
    partner = subquery.partner
FROM (SELECT address_id,
            CASE WHEN cust.addr1 IS NOT NULL THEN TRUE ELSE FALSE END AS customer,
            CASE WHEN suppl.addr1 IS NOT NULL THEN TRUE ELSE FALSE END AS supplier,
            CASE WHEN partn.addr1 IS NOT NULL THEN TRUE ELSE FALSE END AS partner
      FROM address AS pa
      LEFT OUTER JOIN cust_original AS cust
        ON (pa.addr1 = cust.addr1 AND pa.addr2 = cust.addr2 AND pa.city = cust.city
            AND pa.state = cust.state AND CAST(cust.zip AS VARCHAR(5)) = CAST(pa.zip AS VARCHAR(5)))
      LEFT OUTER JOIN supp_original AS suppl
        ON (pa.addr1 = suppl.addr1 AND pa.addr2 = suppl.addr2 AND pa.city = suppl.city
            AND pa.state = suppl.state AND CAST(pa.zip AS VARCHAR(5)) = CAST(CAST(suppl.zip AS VARCHAR(25)) AS VARCHAR(5)))
      LEFT OUTER JOIN partner_original AS partn
        ON (pa.addr1 = partn.addr1 AND pa.addr2 = partn.addr2 AND pa.city = partn.city
            AND pa.state = partn.state AND CAST(pa.zip AS VARCHAR(5)) = CAST(CAST(partn.zip AS VARCHAR(25)) AS VARCHAR(5)))) AS subquery
WHERE dummy.address_id = subquery.address_id;

This query performs the following operations:

• Selects the customer, supplier, and partner values for each row in the dummy table from a subquery that joins the dummy table with the cust_original, supp_original, and partner_original tables.
• Sets the customer, supplier, and partner columns in the dummy table to the values returned by the subquery.
• Updates only the rows in the dummy table that have a matching address_id in the subquery.

The above is the detailed content of How to Update PostgreSQL Rows with Values from a Subquery?. For more information, please follow other related articles on the PHP Chinese website!