一开始,参考网上的逐条更新办法:
declare @yuyueskid varchar(40)
declare @shangkesj datetimedeclare @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
完美解决