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