Update Query With Joins and Group by Clause

Question

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?

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

score 3 · Accepted Answer · answered Feb 04 '11 at 16:36

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)

In the subselect under EXISTS, in the WHERE clause, shouldn't `id` be prefixed with `TABLE1`? And ditto for the other subselect. — Andriy M, Feb 04 '11 at 18:09
@Andriy M: You can, but the answer already explains that the optimizer see column references not having a table alias to be associated properly. — OMG Ponies, Feb 04 '11 at 18:13
FYI, for some reason I didn't have success with leaving the alias out. It appeared to pull from the wrong table. And I did have success at assigning an alias on the update table. — Mafu Josh, Jan 16 '15 at 17:06

Tony Andrews · Answer 2 · 2011-02-05T13:51:12.310

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.

Won't that write NULL when there's no matching `e.itemid` record? — OMG Ponies, Feb 04 '11 at 16:41
@OMG Ponies, you will have your upvote once I have recovered from the bad memories of the very problem of overwriting with nulls :) — Ronnis, Feb 04 '11 at 22:20

Update Query With Joins and Group by Clause

2 Answers2