知道这里有什么问题吗?如何将以下 Sybase 查询转换为 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 后,我收到此错误“单行子查询返回多于一行”
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)
您应该使用密钥保留视图来使用此查询进行更新。 Oracle 不允许在 Update 查询中使用 From 子句或仅使用关联更新或合并更新。
Post https://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table
相关更新:
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 )
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)