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