Error in oracle query Single row subquery returns more than one row

Question

Any Idea what is the problem here ? How to convert the below Sybase query to Oracle.

Sybase query

Update student  s1 set s1.delay = (select  date1 - date2 from cource c where c.ID = c1.ID and 
c.value  ='On' and c.Act_id = 
select max(Act_id) from cource c2 where c2.Id = C.id and c2.value ='On')
from student s1, Cource c1
where c1.id = s1.id and 
c1.value ='On' and 
s1.status = 'active' and 
s1.currentprofile = s1.prevProfile

After converting the above query to oracle I am getting this error "Single row sub query returns more than one row"

Update student  s1 set s1.delay = (select (select  date1 - date2 from cource c where c.ID = c1.ID and             
c.value  ='On' and c.Act_id = 
select max(Act_id) from cource c2 where c2.Id = C.id and c2.value ='On')
from student s1, Cource c1
where c1.id = s1.id and 
c1.value ='On' and 
s1.status = 'active' and 
s1.currentprofile = s1.prevProfile)
Where Exists
(select (select  date1 - date2 from cource c where c.ID = c1.ID and c.value  ='On' and c.Act_id = 
select max(Act_id) from cource c2 where c2.Id = C.id and c2.value ='On')
from student s1, Cource c1
where c1.id = s1.id and 
c1.value ='On' and 
s1.status = 'active' and 
s1.currentprofile = s1.prevProfile)

@William Robertson: You are right and the reason I didn't emphasize on it as it was not sure for me the scope of the changes interms of renaming. — Sujitmohanty30
– Sujitmohanty30, Commented Sep 1, 2020 at 11:13
Sample data, desired results, and an explanation of the logic would help. — Gordon Linoff
– Gordon Linoff, Commented Sep 1, 2020 at 11:14

Atif · Accepted Answer · 2020-09-01 09:34:58Z

1

You should use key presered view to update using this query. Oracle does not allow From clause in Update query Or use Correlated update or Merge with update only.

Post

Correlated update:

UPDATE table1 t1
   SET (name, desc) = (SELECT t2.name, t2.desc
                         FROM table2 t2
                        WHERE t1.id = t2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id )

answered Sep 1, 2020 at 9:34

Atif

2,2202 gold badges11 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sujitmohanty30 · Accepted Answer · 2020-09-01 10:29:37Z

0

Sybase query(for explain purpose):

update student s1 
   set s1.delay = 
   (select (date1 -date2) 
      from cource c 
     where ****c.id = c1.id****(II)
       and c.value  ='On' 
       and c.Act_id = select max(Act_id) 
                        from cource c2 
                       where c2.Id = C.id 
                         and c2.value ='On')
  ****from student s1
     , cource c1****(I)
 where c1.id = s1.id 
   and c1.value ='On' 
   and s1.status = 'active' 
   and s1.currentprofile = s1.prevProfile;

While updating there are two main conditions we can see,

First, if you see the part ****from student s1 , cource c1****(I) this one makes sure you are only updating the rows from student table which has matching id in cource table along with some more conditions, AND because Oracle don't allow such type of checks directly in the from clause of the update statement, it can be replaced with exists clause which can be seen in the below Oracle query.
Second, the part ****c.id = c1.id****(II) in above Sybase query makes sure it further only fetch the data for the set clause by co-relate to the ids we found in the first step and for Oracle this we need to replace with the actual table which is being updated i.e. student because we already make sure with exists in the first step what ids has to be updated.

Oracle Query(actual query):

update student s1 
   set s1.delay = (select (date1 - date2)
                     from cource c 
                    where c.id = s1.id 
                      and c.value  ='On' 
                      and c.act_id = select max(act_id) 
                                       from cource c2 
                                      where c2.Id = c.id 
                                        and c2.value ='On')
from student s1
where s1.status = 'active' 
  and s1.currentprofile = s1.prevprofile
  and exists (select 1 
                from cource c1
               where c1.id = s1.id 
                 and c1.value ='On');

answered Sep 1, 2020 at 10:29

Sujitmohanty30

3,3362 gold badges9 silver badges24 bronze badges

4 Comments

sujai Over a year ago

I have tried. Can you help on this resolve stackoverflow.com/questions/63775974/…

Sujitmohanty30 Over a year ago

@sujai , I was already checking will take some time to answer but I don't understand the part where it does the sysdate - pd.exittime which is part of else clause of condition pd.exittime != null ..... does it make sense to do sysdate - null ? the result will be null only

sujai Over a year ago

it is typo mistake. I have asked to updated the question

Sujitmohanty30 Over a year ago

@sujai, I answered there but don't know whether it worked or not ?

Collectives™ on Stack Overflow

Error in oracle query Single row subquery returns more than one row

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related