PostgreSQL - 使用子查询更新多列值

PostgreSQL - Using a Subquery to Update Multiple Column Values

提问by user558122

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 ?

我怎样才能在 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)

回答by a_horse_with_no_name

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

如果 ship_charge 没有被索引，这应该比两个子选择更快。如果将 ship_charge 编入索引，则可能没有太大区别

---

---

Edit

编辑

Starting with Postgres 9.5 this can also be written as:

从 Postgres 9.5 开始，这也可以写成：

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

回答by wildpeaks

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.

如果要一次更新 table1 中的多行，也可以在子查询中返回多行。

回答by Bohemian

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; 

回答by mu is too short

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:

来自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.

根据标准，列列表语法应该允许从单个行值表达式分配列列表，例如子选择：

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

目前还没有实现——源必须是一个独立表达式列表。

回答by rhtsjz

As the official documentsays: you can use the standard update Synopsis of PostgreSQL update
UPDATE table SET { column = { expression | DEFAULT } | ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] [ FROM from_list ] [ WHERE condition ]

正如官方文档所说：您可以使用标准更新 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:

所以你可以像这样使用 write：

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;

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

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

回答by philix

Using UPDATE FROMis a good solution when you don't have simple subselects. In this UPDATEI wanted to set the event_profile_idof the photostable to be the owner (event profiles are owners too) of the photo set the photo belongs to.

UPDATE FROM当您没有简单的子选择时，使用是一个很好的解决方案。在此，UPDATE我想event_profile_id将photos表的 设置为照片所属的照片集的所有者（事件配置文件也是所有者）。

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;

回答by Lucio Mollinedo

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+:

以下 sql 在 Postgresql 11 上进行了测试，虽然它在 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
;