Converting nested sql update query to JOIN operation

Question

I was wondering how can I change my query to JOIN one with the same result:

update CDR 
set CDR_TYPE = 'ON_NET' 
where anum in (select subscriber 
               from DEGREE 
               where sub_type = 'MTN')
  and bnum in (select subscriber 
               from DEGREE 
               where sub_type = 'MTN')

FYI: I am using ORACLE database.

Best Regards.

Sql server or mysql or oracle ? They have different syntax gor join/ update operation — Mudassir Hasan, Apr 19 '14 at 07:12
I don't think oracle supports `join` in `update` statements. — Ashish Gaur, Apr 19 '14 at 07:17
You might wanna look at this answer => http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join — Ashish Gaur, Apr 19 '14 at 07:26
If the below solution from m hasan doesn't work. You could always use a WITH clause to load your subquery, then you don't have to run that query twice. — Devon, Apr 19 '14 at 07:29

score 1 · Answer 1 · answered Apr 19 '14 at 17:54

You could use a WITH clause to get rid of the repeated subquery.

WITH subquery AS (select subscriber 
               from DEGREE 
               where sub_type = 'MTN')
UPDATE cdr
SET cdr_type = 'ON_NET' 
WHERE anum IN (subquery)
  AND bnum IN (subquery);

score 0 · Answer 2 · answered Apr 19 '14 at 07:21

0

UPDATE 
(SELECT cdr_type 
 FROM cdr c
 INNER JOIN degree d
 ON c.anum = d.subscriber 
    AND c.bnum = d.subscriber
 WHERE d.sub_type = 'MTN'
) t
SET t.cdr_type = 'ON_NET'

answered Apr 19 '14 at 07:21

Mudassir Hasan

26,910
19
95
126

Just as an onlooker, that is a pretty interesting query. Mind explaining how the subquery works in it? – Devon Apr 19 '14 at 07:25
It should update more than 52m records while it just update 18k of them! – Ali Apr 19 '14 at 08:26

Converting nested sql update query to JOIN operation

2 Answers2