CODEWITHSUNDEEP
sqloracle-databasejoinnested
Ali
Ali

Reputation: 1879

Converting nested sql update query to JOIN operation

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.

Upvotes: 1

Views: 291

Answers (2)

Devon Bessemer
Devon Bessemer

Reputation: 35357

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

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

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'

Upvotes: 0

Related Questions