sql:
update (select n.status n_status from cccccc c, nnnnnnnn n
where c.status = 1 and n.status = 1 and c.batch_no = n.batch_no
and c.prepare_time< sysdateorder by c.prority desc)
set n_status = 233333 where rownum<= 100;
这条sql语句在oracle9i中可以正常执行,但到了oracle10G中就不能执行提示对视图ora-01732:此视图的数据操纵操纵非法(9i和10G只是在应用中发现,是否其他版本有问题还没测试),
但在10g中去掉orader by和rownum都就没问题但这两个都是我所需要的。
请教在10g中怎么写sql可正常执行。并orader by和rownum!
update (select n.status n_status,rownum rn from cccccc c, nnnnnnnn n
where c.status = 1 and n.status = 1 and c.batch_no = n.batch_no
and c.prepare_time< sysdateorder by c.prority desc)
set n_status = 233333 where rn<= 100;
試哈這樣看可以不啊?
原帖由 jack198409 于 2008-4-5 10:38 发表
update (select n.status n_status,rownum rn from cccccc c, nnnnnnnn n
where c.status = 1 and n.status = 1 and c.batch_no = n.batch_no
and c.prepare_time< sysdateorder by c.prority desc)
set n_status = 233333 where rn
还是不行,这个在9i上也不行,报的错误是对视图操作非法!