ID NAME ---------- -------------------------------------------------------------------------------- a b c d
SQL> update a set a.id=(select count(*)+1 from a b where b.name<a.name);
4 rows updated
SQL> select * from a;
ID NAME ---------- -------------------------------------------------------------------------------- 1 a 2 b 3 c 4 d
SQL>
创建一个序列,从1开始,上限无限制 update 表A a set a.字段1 =youSeq.nextVal order by a.字段2 asc
--试着写一个 没有测试 create or replace procedure updatepro as cur sys_refcursor; v_var number; i number:=1; begin open cur for select row_number() over (order by a.字段2) rn from 表A for update fetch cur into v_var loop exit when cur%notfound; update a set a.字段1=i where current of cur; i:=i+1; commit; end loop; close cur; end;
方法1: 建立一个sequence,update ... set ...= sequence.nextval 方法2,没验证过,不知道行不行,性能可能也不好: update ... set field_a = (select max(field_a) + 1 from ...) 方法3: 存储过程实现
建议采取SEQUENCE。 简单更新可采取下面方式 update tableA T set T.col1 = (select rn from (select T2.rowid rid, row_number() over(order by T2.col2) RN from tableA T2) where rid = T.rowid)
1、创建SEQ序列 create sequence SEQ_GC minvalue 1 maxvalue 999999999999999999999999999 start with 1 increment by 1 cache 20; 2、更新数据 update 表A a set a.字段1 =SEQ_GC.NEXTVAL order by a.字段2 asc
ID NAME
---------- --------------------------------------------------------------------------------
a
b
c
d
SQL> update a set a.id=(select count(*)+1 from a b where b.name<a.name);
4 rows updated
SQL> select * from a;
ID NAME
---------- --------------------------------------------------------------------------------
1 a
2 b
3 c
4 d
SQL>
update 表A a set a.字段1 =youSeq.nextVal order by a.字段2 asc
create or replace procedure updatepro
as
cur sys_refcursor;
v_var number;
i number:=1;
begin
open cur for select row_number() over (order by a.字段2) rn from 表A for update
fetch cur into v_var
loop
exit when cur%notfound;
update a set a.字段1=i where current of cur;
i:=i+1;
commit;
end loop;
close cur;
end;
建立一个sequence,update ... set ...= sequence.nextval
方法2,没验证过,不知道行不行,性能可能也不好:
update ... set field_a = (select max(field_a) + 1 from ...)
方法3:
存储过程实现
简单更新可采取下面方式
update tableA T
set T.col1 = (select rn
from (select T2.rowid rid,
row_number() over(order by T2.col2) RN
from tableA T2)
where rid = T.rowid)
1.楼上所说,创建一个sequence ,然后用sequence.nextval实现
2.创建一个自增长列:[AUTOID] [int] IDENTITY (1, 1) NOT NULL ,用这个列实现
3.用select max(*) + 1 from...来实现
update a set id=rownum+1-1;
create sequence SEQ_GC
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
2、更新数据
update 表A a set a.字段1 =SEQ_GC.NEXTVAL order by a.字段2 asc