我在oracle9i数据库中想提取100条,然后循环把每条记录的state字段变成1,代码如下:Statement smt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String selCode = " select rownum,CONTENT,STATE from HR.YD_MSGDB where STATE=0 and rownum<100 ";//SQL语句
ResultSet rs = smt.executeQuery(selCode);
while (rs.next())
{
rs.updateString("state","1");
rs.updateRow();
}
竟然出错,说java.sql.SQLException: ORA-01446: 无法从含 DISTINCT, GROUP BY 等子句的视图中选择ROWID。如果我把rownum<100去掉,程序就一下问题就没有了,这是怎么回事啊?请高手指教,因为数据量太大,我必须要加rownum!我也没用到DISTINCT, GROUP BY ,更没用到视图!奇怪,请指教,先谢谢了!
String selCode = " select rownum,CONTENT,STATE from HR.YD_MSGDB where STATE=0 and rownum<100 ";//SQL语句
ResultSet rs = smt.executeQuery(selCode);
while (rs.next())
{
rs.updateString("state","1");
rs.updateRow();
}
竟然出错,说java.sql.SQLException: ORA-01446: 无法从含 DISTINCT, GROUP BY 等子句的视图中选择ROWID。如果我把rownum<100去掉,程序就一下问题就没有了,这是怎么回事啊?请高手指教,因为数据量太大,我必须要加rownum!我也没用到DISTINCT, GROUP BY ,更没用到视图!奇怪,请指教,先谢谢了!
真的很奇怪,请教高手!
改成下边应该可以:
select rownum, CONTENT, STATE from (select CONTENT,STATE from HR.YD_MSGDB where STATE=0) t_temp where rownum<100
假设表的主键是CODE:
select CONTENT,STATE from HR.YD_MSGDB where code in (select code from (select code from HR.YD_MSGDB where state=0) where rownum<100)
改成这样试试,别名跟表一样:
select rownum, CONTENT, STATE from (select CONTENT,STATE from HR.YD_MSGDB where STATE=0) HR.YD_MSGDB where rownum<100
这样是肯定不对的.因为rownum是根据记录数增加的,不能做为条件.