PostgreSQL - Using a Subquery to Update Multiple Column Values

Question

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)

Answer 1

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