我需要能够使用子查询的结果更新表上的多个列。一个简单的例子如下所示 -
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)
如果你想避免两次子选择,查询可以这样重写:
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 被索引,它可能不会产生太大的影响
Edit
从 Postgres 9.5 开始,这也可以写成:
UPDATE table1
SET (col1, col2) = (SELECT min(ship_charge), max(ship_charge) FROM orders)
WHERE col4 = 1001
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)