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

Question

我需要能够使用子查询的结果更新表中的多个列。一个简单的示例如下所示 -

UPDATE table1
SET (col1, col2) =
  ((SELECT MIN (ship_charge), MAX (ship_charge) FROM orders))
WHERE col4 = 1001; 

我如何在PostgreSQL中实现这个？

谢谢任何提示！

更新：很抱歉我的示例对我的实际用例来说太简单了。下面的查询更准确 -

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)

- user558122

---

可能是[使用子查询更新Postgres中的表行]的重复问题（https://dev59.com/N2025IYBdhLWcg3wChOb）。 - Roman Starkov

Answer 1

如果您想避免使用两个子查询，可以将查询重写为以下形式：

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

如果ship_charge没有被索引，这种方法比使用两个子查询更快。如果ship_charge已经被索引，那么两种方法可能没有太大的区别。

---

编辑 从Postgres 9.5开始也可以用以下方式书写：

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

- user330315

Answer 2

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

如果你想一次更新table1中的多行数据，可以在子查询中返回多行。

- wildpeaks

Answer 3

这不是做这件事情最有效的方法，但它很简单：

UPDATE table1 SET
col1 = (SELECT MIN (ship_charge) FROM orders),
col2 = (SELECT MAX (ship_charge) FROM orders)
WHERE col4 = 1001; 

- Bohemian

Answer 4

一种选择（但不是唯一的选择）是使用两个单独的子查询：

update table1
set col1 = (select min(ship_charge) from orders),
    col2 = (select max(ship_charge) from orders)
where col4 = 1001;

来自PostgreSQL 9.0的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.

- mu is too short

Answer 5

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

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;

- felipecrv

Answer 6

正如官方文档所述：您可以使用标准的PostgreSQL update语法进行更新操作。

UPDATE table
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition ]

那么您可以这样编写：

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

- rhtsjz

Answer 7

我需要在一个表上进行多个插入操作，从两个没有共同列的表中获取数据，并忽略已经存在的记录。

以下 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
;

- Lucio Mollinedo