SQL游标循环更新表数据

方法一：使用游标循环更新

 

========================
/*游标*/
Declare T_TrEB_Change_cursor cursor scroll for select ver_id  from   T_TrEB_Change order  by Check_Time asc
open T_TrEB_Change_cursor    --打开游标
Declare @ver_id varchar(40)  
declare @start int           --定义开始值
set @start=0                 --赋默认值
--declare @end int 
--select @end=count(*) from T_TrEB_Change 
fetch first from T_TrEB_Change_cursor into @ver_id    --取表中第一条ID
while(@@FETCH_STATUS=0)
begin 
 set @start=@start+1         
  update T_TrEB_Change set CHG_TMS_CNT =@start where Ver_id =@ver_id  --循环更新
  fetch next from T_TrEB_Change_cursor into @ver_id
 end
close T_TrEB_Change_cursor
deallocate T_TrEB_Change_cursor

 方法二： 使用SQL变量循环更新

use Customs_QP 
go
update T_TrEB_Change set CHG_TMS_CNT=0   
go 
update T_TrEB_Change set CHG_TMS_CNT=1 where Ver_id in(select top 1 ver_id from T_TrEB_Change order by Check_Time asc) 
go
declare @num int
set @num=2
declare @total int
select @total=count(*) from T_TrEB_Change 
while(@num<=@total)
Begin 
update T_TrEB_Change set CHG_TMS_CNT =@num   where Ver_id  in
(
select top 1  Ver_id from T_TrEB_Change  where CHG_TMS_CNT =0  order by Check_Time asc
)
set @num=@num+1
End
select Check_Time,CHG_TMS_CNT,* from T_TrEB_Change   order by  Check_Time 

 

转载于:https://www.cnblogs.com/CW918729720/p/10321354.html