CODEWITHSUNDEEP
sqloracle-databasejoinsql-updatesubquery
saloni
saloni

Reputation: 47

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

Upvotes: 1

Views: 71

Answers (3)

Atif
Atif

Reputation: 2210

You can use Key preserved view to write this update as below:

UPDATE (SELECT st.age as age, 
                st.promoted,
                sc.age,
               st.currentClass,
               sc.currentClass,
               st.currentCource,
               sc.currentCource
          FROM Student st INNER JOIN
               School sc
         ON (st.id = sc.id AND st.currentClass = sc.currentClass AND st.currentCource = sc.currentCource)
         WHERE st.status IN ('1','7','2','5'))
   SET age = CASE WHEN st.promoted != 'fail' THEN 1 ELSE sc.age END;

You can refer to this post where I have responded how can Sybase update query can be converted to Oracle.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132580

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

Upvotes: 0

GMB
GMB

Reputation: 222492

Oracle does not support joins in update statements. You can use a correlated subquery instead - all the join does is filtering, so exists should do it:

update student st 
set st.age = 20 
where 
    st.status in(1, 7, 2, 5)
    and exists (
        select 1 
        from school sc 
        where 
            st.id = sc.id  
            and st.currentClass = sc.currentClass  
            and st.currentCource = sc.currentCource 
    )

Upvotes: 1

Related Questions