rownum<=100 order by time 楼主是想按时间排序,前100个进行更新吧。可是这么写是错误的,这里先取rownum再order by,不能得到楼主想要的结果 update satab a set flag='new' where (select count(1) from satab where time>to_date('2009-10-03 10:00:00','YYYY-MM-DD HH24:MI:SS') and time<=a.time)<=100
update satab a set flag='new' where time>to_date('2009-10-03 10:00:00','YYYY-MM-DD HH24:MI:SS') and (select count(1) from satab where time>to_date('2009-10-03 10:00:00','YYYY-MM-DD HH24:MI:SS') and time <=a.time) <=100
select语句不就是找出要更新的记录吗 并到update的where条件后面去了 这不是你要的结果吗? 只是你原先的查询语句有问题,所以改了一下 如果你只是要合并update satab set flag = 'new' where time>to_date('2009-10-03 10:00:00','YYYY-MM-DD HH24:MI:SS') and rownum <=100 就行了
在pl/sql是可以用returning执行dml然后返回记录的,比如set serveroutput on size 1000000 declare w_id integer; begin update mytable set id = 2 where id = 1 returning id into w_id; dbms_output.put_line(w_id); end; /但纯粹的sql是做不到的。
楼主是想按时间排序,前100个进行更新吧。可是这么写是错误的,这里先取rownum再order by,不能得到楼主想要的结果
update satab a set flag='new'
where (select count(1) from satab
where time>to_date('2009-10-03 10:00:00','YYYY-MM-DD HH24:MI:SS')
and time<=a.time)<=100
where time>to_date('2009-10-03 10:00:00','YYYY-MM-DD HH24:MI:SS')
and (select count(1) from satab
where time>to_date('2009-10-03 10:00:00','YYYY-MM-DD HH24:MI:SS')
and time <=a.time) <=100
并到update的where条件后面去了
这不是你要的结果吗?
只是你原先的查询语句有问题,所以改了一下
如果你只是要合并update satab set flag = 'new'
where time>to_date('2009-10-03 10:00:00','YYYY-MM-DD HH24:MI:SS') and rownum <=100
就行了
declare
w_id integer;
begin
update mytable
set id = 2
where id = 1
returning id
into w_id;
dbms_output.put_line(w_id);
end;
/但纯粹的sql是做不到的。