PostgreSQL - Using a Subquery to Update Multiple Column Values

I need to be able to update multiple columns on a table using the result of a subquery. A simple example will look like below -

UPDATE table1
SET (col1, col2) =
  ((SELECT MIN (ship_charge), MAX (ship_charge) FROM orders))
WHERE col4 = 1001; 

How can I do this in PostgreSQL ?

Thanks for any tips!

UPDATE: I apologize for making the sample too simple for my actual use-case开发者_开发问答. The query below is more accurate -

UPDATE table1
SET    (TOTAL_MIN_RATE, TOTAL_MAX_RATE) = (SELECT AVG(o.MIN_RATE), AVG(o.MAX_RATE)
                           FROM   ORDR o INNER JOIN table2 ba ON (o.PAY_ACCT_ID = ba.ACCT_ID) 
                                         INNER JOIN table3 mb ON (ba.BANK_ID = mb.BANK_ID)
                               WHERE  ba.CNTRY_ID = table1.CNTRY_ID AND 
                                              o.STUS_CD IN ('01','02','03','04','05','06') AND
                                  ((o.FRO_CRNCY_ID = table1.TO_CRNCY_ID AND o.TO_CRNCY_ID = table1.FRO_CRNCY_ID) OR
                                   (o.TO_CRNCY_ID = table1.TO_CRNCY_ID AND o.FRO_CRNCY_ID = table1.FRO_CRNCY_ID))   
                               GROUP BY ba.CNTRY_ID)

If you want to avoid two subselects, the query can be rewritten like this:

UPDATE table1
  SET col1 = o_min, col2 = o_max
FROM ( 
    SELECT min(ship_charge) as o_min, 
           max(ship_charge) as o_max
    FROM orders
) t 
WHERE col4 = 1001

If ship_charge is not indexed, this should be faster than two subselects. If ship_charge is indexed, it probably doesn't make a big difference

Edit

Starting with Postgres 9.5 this can also be written as:

UPDATE table1
  SET (col1, col2) = (SELECT min(ship_charge), max(ship_charge) FROM orders)
WHERE col4 = 1001

UPDATE table1
SET
    col1 = subquery.min_value,
    col2 = subquery.max_value
FROM
(

    SELECT
        1001 AS col4,
        MIN (ship_charge) AS min_value,
        MAX (ship_charge) AS max_value
    FROM orders
) AS subquery
WHERE table1.col4 = subquery.col4

You can also return multiple rows in the subquery if you want to update multiple rows at once in table1.

This isn't the most efficient way to do this, but it's simple:

UPDATE table1 SET
col1 = (SELECT MIN (ship_charge) FROM orders),
col2 = (SELECT MAX (ship_charge) FROM orders)
WHERE col4 = 1001; 

One option (but not the only one) is to use two separate sub-queries:

update table1
set col1 = (select min(ship_charge) from orders),
    col2 = (select max(ship_charge) from orders)
where col4 = 1001;

From the fine manual for PostgreSQL 9.0's UPDATE:

According to the standard, the column-list syntax should allow a list of columns to be assigned from a single row-valued expression, such as a sub-select:

UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
 WHERE salesmen.id = accounts.sales_id);

This is not currently implemented — the source must be a list of independent expressions.

As the official document says: you can use the standard update Synopsis of PostgreSQL update

UPDATE table
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition ]

So you can use write like this:

UPDATE table1
SET TOTAL_MIN_RATE = subQuery."minRate",
 TOTAL_MAX_RATE = subQuery.maxRate
FROM
    (
        SELECT
            AVG (o.MIN_RATE) AS minRate,
            AVG (o.MAX_RATE) AS maxRate
        FROM
            ORDR o
        INNER JOIN table2 ba ON (o.PAY_ACCT_ID = ba.ACCT_ID)
        INNER JOIN table3 mb ON (ba.BANK_ID = mb.BANK_ID)
        WHERE
            ba.CNTRY_ID = table1.CNTRY_ID
        AND o.STUS_CD IN (
            '01',
            '02',
            '03',
            '04',
            '05',
            '06'
        )
        AND (
            (
                o.FRO_CRNCY_ID = table1.TO_CRNCY_ID
                AND o.TO_CRNCY_ID = table1.FRO_CRNCY_ID
            )
            OR (
                o.TO_CRNCY_ID = table1.TO_CRNCY_ID
                AND o.FRO_CRNCY_ID = table1.FRO_CRNCY_ID
            )
        )
        GROUP BY
            ba.CNTRY_ID
    ) subQuery;

Or a more simple way:

UPDATE table1
SET (
    TOTAL_MIN_RATE,
    TOTAL_MAX_RATE
) = (
    SELECT
        AVG (o.MIN_RATE) AS minRate,
        AVG (o.MAX_RATE) AS maxRate
    FROM
        ORDR o
    INNER JOIN table2 ba ON (o.PAY_ACCT_ID = ba.ACCT_ID)
    INNER JOIN table3 mb ON (ba.BANK_ID = mb.BANK_ID)
    WHERE
        ba.CNTRY_ID = table1.CNTRY_ID
    AND o.STUS_CD IN (
        '01',
        '02',
        '03',
        '04',
        '05',
        '06'
    )
    AND (
        (
            o.FRO_CRNCY_ID = table1.TO_CRNCY_ID
            AND o.TO_CRNCY_ID = table1.FRO_CRNCY_ID
        )
        OR (
            o.TO_CRNCY_ID = table1.TO_CRNCY_ID
            AND o.FRO_CRNCY_ID = table1.FRO_CRNCY_ID
        )
    )
    GROUP BY
        ba.CNTRY_ID
);

Using UPDATE FROM is a good solution when you don't have simple subselects. In this UPDATE I wanted to set the event_profile_id of the photos table to be the owner (event profiles are owners too) of the photo set the photo belongs to.

UPDATE photos
SET event_profile_id=photos_and_events.event_profile_id
FROM (
  SELECT
    ph.id photo_id,
    pr.id event_profile_id
  FROM photos ph, profiles pr, photo_sets ps
  WHERE ph.main_photo_set_id=ps.id AND ps.owner_profile_id=pr.id
) AS photos_and_events
WHERE photos.id=photos_and_events.photo_id;

I needed to do multiple inserts on a table taking the data from two tables without common columns between them and ignoring records already present.

The following sql was tested on Postgresql 11, althought it should work fine on v9+:

WITH permission_info AS (
    SELECT id
    FROM permission
    WHERE permission."key" LIKE 'prefix_for_admin_%'
), role_info AS (
    SELECT id 
    FROM role
    WHERE role."name" = 'Admin'
)
INSERT INTO role_permission_table
(
    role_id,
    permission_id
)
    SELECT role_info.id, permission_info.id FROM role_info, permission_info

ON CONFLICT DO NOTHING
;