oracle 使用连接和分组更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4900305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update Query With Joins and Group by Clause
提问by zSynopsis
I have the following query and I'm trying to update table1 with the Total amount. Is there anyway to do this in 1 step?
我有以下查询，我正在尝试使用总金额更新 table1。有没有办法在 1 步中做到这一点？
select e.id
, p.id
, case
when count(distinct e.item) = 1 then 100
when count(distinct e.item) = 2 then 150
when count(distinct e.item) = 3 then 200
when count(distinct e.item) = 4 then 225
when count(distinct e.item) = 5 then 275
when count(distinct e.item) = 6 then 325
when count(distinct e.item) = 7 then 375
when count(distinct e.item) = 8 then 450
when count(distinct e.item) = 8 then 470
end as TotalPay
from table1 p
join table2 e on e.id = '111111'
and p.id=e.itemid
group by e.id, p.id
回答by OMG Ponies
Use:
用：
UPDATE TABLE1
SET total = (SELECT CASE
WHEN COUNT(DISTINCT t2.item) = 1 THEN 100
WHEN COUNT(DISTINCT t2.item) = 2 THEN 150
WHEN COUNT(DISTINCT t2.item) = 3 THEN 200
WHEN COUNT(DISTINCT t2.item) = 4 THEN 225
WHEN COUNT(DISTINCT t2.item) = 5 THEN 275
WHEN COUNT(DISTINCT t2.item) = 6 THEN 325
WHEN COUNT(DISTINCT t2.item) = 7 THEN 375
WHEN COUNT(DISTINCT t2.item) = 8 THEN 450
WHEN COUNT(DISTINCT t2.item) = 9 THEN 470
END
FROM TABLE2 t2
WHERE t2.itemid = id
AND t2.id = '111111'
GROUP BY t2.id, t2.itemid)
WHERE EXISTS(SELECT NULL
FROM TABLE2 t
WHERE t.itemid = id
AND t.id = '111111')
- The WHERE clause is necessary, otherwise all the TABLE1 rows will be processed. Those who don't have related TABLE2 rows, would've been updated to
NULL - Oracle (IME, up to 10g) doesn't support JOINs in an UPDATE clause like MySQL & SQL Server -- you have to use a subquery (correlated in this example). It also doesn't allow you to define a table alias for the table being updated, so when a table alias is omitted like you see in the example -- the column is coming from the table without an alias (the one being updated)
- WHERE 子句是必需的，否则将处理所有 TABLE1 行。那些没有相关 TABLE2 行的人会被更新为
NULL - Oracle（IME，最高 10g）不支持像 MySQL 和 SQL Server 这样的 UPDATE 子句中的 JOIN——您必须使用子查询（在本例中是相关的）。它也不允许您为正在更新的表定义表别名，因此当您在示例中看到的表别名被省略时 - 该列来自没有别名的表（正在更新的表）
回答by Tony Andrews
Try:
尝试：
update table1 p
set TotalPay =
(
select case
when count(distinct e.item) = 1 then 100
when count(distinct e.item) = 2 then 150
when count(distinct e.item) = 3 then 200
when count(distinct e.item) = 4 then 225
when count(distinct e.item) = 5 then 275
when count(distinct e.item) = 6 then 325
when count(distinct e.item) = 7 then 375
when count(distinct e.item) = 8 then 450
when count(distinct e.item) = 8 then 470
end as TotalPay
from table2 e where p.id=e.itemid
and e.id = '111111'
)
As has been pointed out in comments, the above will update all rows in table1 even if there is no match in table2 - in which it will set the column to NULL. To avoid that add a WHERE clause - see OMGPonies's answer.
正如评论中指出的那样，即使在 table2 中没有匹配项，上面也会更新 table1 中的所有行 - 它将列设置为 NULL。为避免这种情况，请添加 WHERE 子句 - 请参阅 OMGPonies 的回答。
