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