Search code examples
sqloracle-databasejoinsql-updatesubquery

Error while executing the below Oracle query


I was trying convert below Sybase query to Oracle query.

update Student set st.age = (case when st.promoted != 'fail' then 1 
else (select sc1.age from school sc1 where st.id = sc1.id ) END)
from Student st ,School sc
where st.id = sc.id
AND st.status in('1','7','2','5')
AND st.currentClass = sc.currentClass 
AND st.currentCource = sc.currentCource ;

But I have tried executing below Oracle query after conversion but getting following error.

update Student st set st.age = (select (case when st.promoted != 'fail' 
then 1 
else (select sc1.age from school sc1 where st.id = sc1.id ) END) 
from School sc   
where st.id = sc.id
AND st.status in('1','7','2','5')
AND st.currentClass = sc.currentClass 
AND st.currentCource = sc.currentCource )
where exists 
   (select 1 from School sc1
where st.id = sc1.id
AND st.status in('1','7','2','5')
AND st.currentClass = sc1.currentClass 
AND st.currentCource = sc1.currentCource);

Query returning : ORA-01427 Single-row subquery returning more than one row. Any one please help


Solution

  • update Student st set st.age = case when st.promoted != 'fail'
                                        then 1 
                                        else (select sc1.age from school sc1
                                              where st.id = sc1.id )
                                        end
    where exists 
       (select 1 from School sc1
    where st.id = sc1.id
    AND st.status in('1','7','2','5')
    AND st.currentClass = sc1.currentClass 
    AND st.currentCource = sc1.currentCource);