Tuesday, January 17, 2012

Using CASE WHEN statement

Suggested dev team to change the following SQL:

update t1 
set b=(SELECT    
               decode(count(*),0,'N','Y')
          FROM t2 
         WHERE t1.id = t2.id);


to the following structruce:

update t1  set b= case when  
                     exists ( select null from t2 where t1.id =t2.id  )
                       then 'Y' 
                       else 'N' end;