使用联接和 Group by 子句更新查询

Question

我有以下查询，我正在尝试使用总金额更新 table1 。 是否有办法一步完成此操作？

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

Answer 1

使用：

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

• WHERE子句是必需的，否则将处理TABLE1的所有行。那些没有相关 TABLE2 行的人将被更新为 NULL
• Oracle（IME，最多 10g）不支持 UPDATE 子句中的 JOIN，例如 MySQL & 。 SQL Server——您必须使用子查询（在本例中是相关的）。它还不允许您为正在更新的表定义表别名，因此当像示例中所示省略表别名时，该列来自没有别名的表（正在更新的表）

Answer 2

尝试：

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

正如注释中所指出的，即使 table2 中没有匹配项，上面的代码也会更新 table1 中的所有行 - 它将把列设置为 NULL。为了避免这种情况，请添加 WHERE 子句 - 请参阅 OMGPonies 的答案。