62,046
社区成员
发帖
与我相关
我的任务
分享
create procedure updateAll
as
begin
declare @id int,@iNewsID int,@count int
select @count=count(iNewsID) from tableName
set @id=1
select top 1 @id=iNewsID from tableName order by iNewsID
if @id <> 1
begin
update tableName set iNewsID=1 where iNewsID=@id
end
set @id=0
declare id_cursor cursor for
select iNewsID from tableName order by iNewsID
open id_cursor
fetch next from id_cursor into @iNewsID
while @@fetch_status = 0
begin
set @id=@id+1;
print @id;
update tableName set iNewsID=@id where iNewsID=@iNewsID
fetch next from id_cursor into @iNewsID
end
close id_cursor
deallocate id_cursor
create procedure updateAll
as
begin
declare @id int,@count int
select @count=count(iNewsID) from tableName --去的所有记录条数
set @id=1
select top 1 @id=iNewsID from tableName order by iNewsID
--如果第一条记录id不是1,就更新成1
if @id <> 1
begin
update tableName set iNewsID=1 where iNewsID=@id
end
set @id=0
while @id<@count
begin
set @id=@id+1;--累加@id,每次取下一条记录来做更新
update tableName a
set iNewsID=@id
from (select top 1 iNewsID from tableName where iNewsID>(@id-1)) b
where a.iNewsID=b.iNewsID
end
end