PostgreSQL-使用子查询更新多个列值 [英] PostgreSQL - Using a Subquery to Update Multiple Column Values
问题描述
我需要能够使用子查询的结果更新表上的多个列。一个简单的示例如下所示-
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;
如何在PostgreSQL中做到这一点?
How can I do this in PostgreSQL ?
感谢任何提示!
更新:对于使示例对于实际用例过于简单,我深表歉意。下面的查询更准确-
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
如果未对ship_charge编制索引,则此应该比两个子选择要快。如果将ship_charge编入索引,则可能没有什么大不同
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
编辑
从Postgres 9.5开始,也可以这样写:
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
这篇关于PostgreSQL-使用子查询更新多个列值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!