如果iid在數據庫中沒有重復,可以這麼寫 Update A Set iid=(Select Count(*) From TableName Where iid<=A.iid) From TableName A
有重復,就麻煩些。刪除這一列,重加上一個自增列。或者按下面示例的方法,借用一下自增列。Create Table TEST(ID Int,Name Varchar(10)) Insert TEST Select 1,'aa' Union All Select 2,'bb' Union All Select 2,'cc' GO Alter Table TEST Add IID Int Identity(1,1)Update TEST Set ID=IIDAlter Table TEST Drop Column IIDSelect * From TEST GO Drop Table TEST GO /* ID Name 1 aa 2 bb 3 cc */
create table A ( iid int, code varchar(10), name varchar(10) )insert A select 1,'001','张三' insert A select 5,'002','李四' insert A select 5,'002','李四' insert A select 10,'005','赵五' select identity(int,1,1) as id,* into # from A delete from Ainsert A select id,code,name from #select * from A
alter table xxx
add IID_EX int identity(1,1) not null
goupdate xxx set IID=IID_EX
go
iid code name
1 001 张三
5 002 李四
10 005 赵五现在我希望iid的值是1、2、3,用code排序
Update A Set iid=(Select Count(*) From TableName Where iid<=A.iid) From TableName A
Insert TEST Select 1,'aa'
Union All Select 2,'bb'
Union All Select 2,'cc'
GO
Alter Table TEST Add IID Int Identity(1,1)Update TEST Set ID=IIDAlter Table TEST Drop Column IIDSelect * From TEST
GO
Drop Table TEST
GO
/*
ID Name
1 aa
2 bb
3 cc
*/
(
iid int,
code varchar(10),
name varchar(10)
)insert A select 1,'001','张三'
insert A select 5,'002','李四'
insert A select 5,'002','李四'
insert A select 10,'005','赵五'
select identity(int,1,1) as id,* into # from A
delete from Ainsert A select id,code,name from #select * from A