sql逐条更新表数据

一开始，参考网上的逐条更新办法：

declare @yuyueskid varchar(40)

declare @shangkesj datetime
declare @yuyuezt int
declare @i int
declare @j int
set @j=(select count(*) from yuyuesk)
set @i=1
while   @i<=@j
begin               
    set @yuyueskid=(select yuyueskid from ( select ROW_NUMBER() over (order by yuyueskid asc ) as Row, yuyueskId from yuyuesk ) as sp  where Row=@i)
    set @shangkesj=(select shangkesj from ( select ROW_NUMBER() over (order by yuyueskid asc ) as Row, shangkesj from yuyuesk ) as sp  where Row=@i)
    set @yuyuezt=(select yuyuezt from ( select ROW_NUMBER() over (order by yuyueskid asc ) as Row, yuyuezt from yuyuesk ) as sp  where Row=@i)
    update               
        yuyuesk
    set
        yuyuezt=4
    where                
        yuyueskId=@yuyueskid and @yuyuezt=1 and @shangkesj<GETDATE()
        set @i=@i+1

end

出现的问题是，虽然可以逐条更新，但是需要把表内的数据全部过一次，如果表内数据太多，效率会非常低。

然后改变思路，先把符合更新条件的数据插入临时表，然后再更新：

declare @yuyueskid varchar(40)
declare @shangkesj datetime
declare @yuyuezt int
declare @i int
declare @j int
select * into #yuyuesk from yuyuesk where yuyuezt=1
set @j=(select count(*) from #yuyuesk)
set @i=1
while   @i<=@j
begin               
    set @yuyueskid=(select yuyueskid from ( select ROW_NUMBER() over (order by yuyueskid asc ) as Row, yuyueskId from #yuyuesk ) as sp  where Row=@i)
    set @shangkesj=(select shangkesj from ( select ROW_NUMBER() over (order by yuyueskid asc ) as Row, shangkesj from #yuyuesk ) as sp  where Row=@i)
    set @yuyuezt=(select yuyuezt from ( select ROW_NUMBER() over (order by yuyueskid asc ) as Row, yuyuezt from #yuyuesk ) as sp  where Row=@i)
    update               
        yuyuesk
    set
        yuyuezt=4
    where                
        yuyueskId=@yuyueskid and @yuyuezt=1 and @shangkesj<GETDATE()
        set @i=@i+1
end

完美解决