How to Update PostgreSQL Table Rows Using a Subquery with Conditional Logic?

Updating PostgreSQL Table Rows Using a Subquery

Consider a scenario where you have an existing table in a Postgres database, and you need to update its rows using values obtained from a subquery. This task can be accomplished using a concise update statement.

In this particular case, the objective is to update rows in the dummy table using values retrieved from a complex subquery that joins multiple tables (cust_original, supp_original, and partner_original) and applies conditional logic to determine the values for customer, supplier, and partner columns.

To perform this update, you can utilize 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 SUBSTRING(cust.zip, 1, 5) = pa.zip)
    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 pa.zip = SUBSTRING(suppl.zip, 1, 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 pa.zip = SUBSTRING(partn.zip, 1, 5))
    WHERE pa.address_id = address_id
) AS subquery
WHERE dummy.address_id = subquery.address_id;

This statement leverages a subquery to derive the values for the customer, supplier, and partner columns. The subquery performs the necessary joins and conditional logic as specified in your provided insert statement.

The outer UPDATE statement then applies these subquery-derived values to the corresponding columns in the dummy table, updating the existing rows based on the address_id field.

This approach is particularly useful when you need to update table rows based on values derived from complex or dynamic calculations or when working with large datasets where traditional row-by-row updates can be inefficient.

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