current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 11 down vote favorite
5

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)
share|improve this question
    
possible duplicate of updating table rows in postgres using subquery –  romkyns Mar 14 '12 at 14:58

5 Answers 5

up vote 18 down vote

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

share|improve this answer
up vote 9 down vote 
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.

share|improve this answer
up vote 4 down vote

Postgres isn't so flexible:

UPDATE table1 SET
col1 = (SELECT MIN (ship_charge) FROM orders),
col2 = (SELECT MAX (ship_charge) FROM orders)
WHERE col4 = 1001;
share|improve this answer
    
The downvote is absolutely unnecessary. It's perfect valid answer –  a_horse_with_no_name Sep 18 '11 at 9:08
    
Yeah. OK, so it's a little simplistic, bot it's a bit harsh to downvote, whoever you are... –  Bohemian♦ Sep 18 '11 at 11:14
    
Downvote is not neccesary, tho saying, that performing query twice is the best answer isnt right too. In a worst case scenarion one could always use a function and store the query result in the local variable. –  ertx Sep 18 '11 at 13:10
up vote 3 down vote

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.

share|improve this answer
up vote 0 down vote

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.