PostgreSQL - Using a Subquery to Update Multiple Column Values

postgres update multiple columns
postgres conditional update
psycopg2 update multiple columns
postgresql update from select same table
postgres update multiple rows subquery
update set multiple fields postgres
select and update in same query postgres
postgres update if condition

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 or Insert (multiple rows and columns) from subquery in , Use the following query for the UPDATE: UPDATE table1. SET col1 = othertable.​col2,. col2 = othertable.col3. FROM othertable. WHERE  UPDATE table1 . SET col1 = othertable.col2, col2 = othertable.col3 . FROM othertable . WHERE othertable.col1 = 123; Use the below query for the INSERT: INSERT INTO table1 (col1, col2) SELECT col1, col2 . FROM othertable. Note: If you are using the SELECT statement to populate the INSERT values then you don't need the VALUES syntax.


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.

Documentation: 9.5: UPDATE, Any expression using the table's columns, and/or columns of other tables The sub-query can refer to old values of the current row of the table being updated. the first query is guaranteed to raise an error if there are multiple id matches. Using a Subquery to Update Multiple Column Values The expression list can include one or more subqueries. Each must return a single row containing one or more values. of columns that the SET clause explicitly or implicitly specifies


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; 

PostgreSQL UPDATE, This tutorial shows you how to use the PostgreSQL UPDATE statement to update values of one or more columns of a table based on a specified condition. If you update values in multiple columns, you use a comma (,) to separate each pair of column and value. The columns that are not on the list retain their original values. Third, determine which rows you want to update in the condition of the WHERE clause. If you omit the WHERE clause, all the rows in the table are updated. PostgreSQL UPDATE examples. We will use the linktable created in the INSERT tutorial for the demonstration.


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.

Using a Subquery to Update Multiple Column Values, The expression list can include one or more subqueries. Each must return a single row containing one or more values. The number of columns that the SET  SQL update using subqueries Last update on February 26 2020 08:07:43 (UTC/GMT +8 hours) In this page, we are discussing the usage of a subquery to update the values of columns with the UPDATE statement.


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 );

PostgreSQL's subqueries: multiple columns, better and faster results , PostgreSQL's subqueries: multiple columns, better and faster results to write the same subquery and get the same output using more or less  SQL: Using ANY with a Multiple Row Subquery. You can use the ANY operator to compare a value with any value in a list. You must place an =, <>, >, <, <= or >= operator before ANY in your query. The following example uses ANY to check if any of the agent who belongs to the country 'UK'. Sample table : agents


update multiple columns using subquery – SQLServerCentral, I am trying to update multiple columns in one table from another table using a subquery. Getting the following error message: Incorrect syntax  Perform the same operation, using a sub-select in the WHERE clause: UPDATE employees SET sales_count = sales_count + 1 WHERE id = (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation'); Attempt to insert a new stock item along with the quantity of stock.


SQL : Multiple Row and Column Subqueries, Multiple row subquery returns one or more rows to the outer SQL statement. Last update on February 26 2020 08:07:42 (UTC/GMT +8 hours) Row Subquery; Multiple Column Subqueries; SQL subqueries using DISTINCT  For the UPDATE. Use: UPDATE table1 SET col1 = othertable.col2, col2 = othertable.col3 FROM othertable WHERE othertable.col1 = 123; For the INSERT. Use: INSERT INTO table1 (col1, col2) SELECT col1, col2 FROM othertable You don't need the VALUES syntax if you are using a SELECT to populate the INSERT values.


PostgreSQL - Sub Queries, PostgreSQL - Sub Queries - A subquery or Inner query or Nested query is a query A subquery can have only one column in the SELECT clause, unless multiple columns in a table can be updated when using a subquery with the UPDATE  Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc. Using subqueries in a FROM clause is known as an inline view. Using subqueries in the WHERE clause is called a nested subquery. Up to 255 nested queries are allowed.