oracle查询出错单行子查询返回多行

2022-01-17 00:00:00 sql-update sql subquery oracle

知道这里有什么问题吗?如何将以下 Sybase 查询转换为 Oracle.

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

sybase 查询

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

将上述查询转换为 oracle 后，我收到此错误单行子查询返回多行"

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)

推荐答案

Sybase查询(用于解释目的):

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. 首先，如果您看到部分 **** from student s1 , c1****(I) 这确保您只更新来自 student 表在 cource 表中具有匹配的 id 以及更多条件，并且因为 Oracle 不允许直接在 update 语句的 >from 子句，可以用 exists 子句替换，可以在下面的 Oracle 查询中看到.

  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.

其次，上述 Sybase 查询中的 ****c.id = c1.id****(II) 部分确保它进一步只获取 <co-relate 的 code>set 子句到我们在第一步中找到的 ids 并且对于 Oracle，我们需要将其替换为正在生成的实际表更新即 student 因为我们已经在第一步中确定了 exists 什么 ids 必须更新.

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 查询(实际查询):

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

相关文章