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)

Answer 1

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,

1. 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.

2. 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');