db2sql中的重设编号顺序

Question

如何重置现有的数据序号？我尝试了alter table tb_shk_user alter column autonum restart with 1，它只适用于新的数据插入--数字将从1开始，但将与现有的1重复。

​

autonum列属性

AUTONUM整数，而非NULL，总是作为标识生成(从1增量1开始)主键

Answer 1

或者这个：

--Save data of your table into temporary table   
create table tmptable as 
(
select * from tb_shk_user 
) 
with data;

--Remove all rows into your table
delete from tb_shk_user ;

--Insert into your table all rows with renumber for autonum, be carefull all columns mus be in select except autonum
insert into tb_shk_user overriding system value
select rownumber() over(order by autonum) as newautonum, username, mobilno
from tmptable;


-- Reposition of autoincrement on max + 1
-- Example if 47896 is the max + 1 of autonum after preceding insert 
alter table tb_shk_user alter column autonum restart with 47896;

Answer 2

我想您想要重新启动，以便有一个很好的id系列，然后您就可以这样做了。

--Temprary table with key autonum and new num ordered
create table tmptable as (
select autonum, rownumber() over(order by autonum) as newautonum
from tb_shk_user 
) with data;

--Update your table with new num
update tb_shk_user f1 overriding system value
set f1.autonum=
(
select f2.newautonum from tmptable f2
where f1.autonum=f2.autonum
)
where exists 
(
select * from tmptable f2
where f1.autonum=f2.autonum
);

-- Reposition of autoincrement on max + 1
-- Example if 47896 is the max + 1 of autonum after preceding update 
alter table tb_shk_user alter column autonum restart with 47896;