日期:2014-05-18  浏览次数:20382 次

更新SQL语句?


create   table   test

(

id   int
)

insert   into   test   select   null

insert   into   test   select   null

insert   into   test   select   null


insert   into   test   select   230
insert   into   test   select   250
insert   into   test   select   433


select   *   from   test
/*
将id列更新为如下形式

id
1
2
3
4
5
6

更新之后把id列设置为identity(1,1)进行递增,不知道可不可以
*/
drop   table   test

------解决方案--------------------
把此列删了再加一个自增列,干嘛改了再增
------解决方案--------------------

create table test(id int)
insert into test select null
insert into test select null
insert into test select null
insert into test select 230
insert into test select 250
insert into test select 433
go

declare @i int
set @i=0

update test set @i=@i+1,id=@i

select * from test
go

drop table test
go
------解决方案--------------------
--这种方式最简单

declare @i int

set @i=0

update 表名 set @i=@i+1,id=@i

------解决方案--------------------
create table test

(

id int
)

insert into test select null

insert into test select null

insert into test select null


insert into test select 230
insert into test select 250
insert into test select 433


select * from test

select identity(int,1,1) as sid,id into #t from test
select id = sid from #t
drop table #t

drop table test
------解决方案--------------------
更新之后把id列设置为identity(1,1)进行递增,不知道可不可以
---------------------
要是这样的话:

alter table test add bh int identity(1,1)

alter table test drop column id

exec sp_rename 'test.bh ', 'id ', 'column '