1.分批更新数据库
declare @x int
set @x=1
while(@x<=51)
begin
begin tran
update UserFavorite set UserFavorite.firstpublishtime = product.lastpublishtime
from UserFavorite,product where UserFavorite.productid = product.id
and UserFavorite.id between (@x-1)* 10000 and @x*10000
commit tran
set @x=@x+1
WAITFOR DELAY '00:00:30'; --等待5秒
end
2.批量更新收藏表
USE [TianYi]
GO
/****** Object: StoredProcedure [dbo].[UpdateUserFavoriteByProductid] Script Date: 01/23/2015 13:28:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[UpdateUserFavoriteByProductid]
as
begin
create table #temp2
(
id int identity(1,1),
productid int,
LastPublishTime datetime
)
declare @ufpage int --@ufnum/10000
declare @ufn int --收藏表循环次数
--取出所有要更新的系列下的所有产品
insert into #temp2 select ID,LastPublishTime from Product where serieID in(select distinct serieID from Product where IsCollectionOperate=1)
set @ufn=1
set @ufpage=1000--假设有10000000条数据
while @ufn<=@ufpage
begin
begin tran
update UserFavorite set UserFavorite.isSeriesUpdate=1,UserFavorite.ispop=1,
UserFavorite.FirstPublishTime = #temp2.LastPublishTime
from UserFavorite inner join #temp2 on UserFavorite.productID=#temp2.productid
where UserFavorite.ID between (@ufn-1)*10000 and @ufn*10000
set @ufn=@ufn+1
WAITFOR DELAY '00:00:02';
commit tran
end
--更新后重置更新状态
update Product set IsCollectionOperate=0 where Id in(select productid from #temp2)
truncate table #temp2 --删除原表数据,并重置自增列
insert into #temp2 select ID,LastPublishTime from Product where Id in(select distinct productID from ProductAudio where IsCollectionOperate=1)
set @ufn=1
set @ufpage=1000--假设有10000000条数据
while @ufn<=@ufpage
begin
begin tran
update UserFavorite set UserFavorite.isread=1,UserFavorite.ispop=1,
UserFavorite.FirstPublishTime = #temp2.LastPublishTime
from UserFavorite inner join #temp2 on UserFavorite.productID=#temp2.productid
where UserFavorite.ID between (@ufn-1)*10000 and @ufn*10000
set @ufn=@ufn+1
WAITFOR DELAY '00:00:02';
commit tran
end
update ProductAudio set IsCollectionOperate=0 where productid in (select productid from #temp2)
end
-------------------------------------------------------------------------------------------------------------------------
3. 批量更新递增列
--申明一个游标
DECLARE MyCursor CURSOR
FOR select id from productaudio where productid = 756407 order by linkurl
declare @x int
set @x=1
--打开一个游标
OPEN MyCursor
--循环一个游标
DECLARE @id int
FETCH NEXT FROM MyCursor INTO @id
WHILE @@FETCH_STATUS =0
BEGIN
update productaudio set audioname = @x where id = @id
set @x=@x+1
FETCH NEXT FROM MyCursor INTO @id
END
--关闭游标
CLOSE MyCursor
--释放资源
DEALLOCATE MyCursor