----删除设计—动漫动画的免费作品-------
WHILE ( 1 = 1 )
BEGIN
UPDATE TOP ( 1000 )
dbo.az_photo
SET filestatus=3,ModifiedOn=GETDATE()
WHERE sid in (4,5,6,7) and filestatus=2
IF @@ROWCOUNT < 1000
BREAK
WAITFOR DELAY '00:00:01'
END
----删除2011年1月1日—2017年12月31日之间免费作品下载量为0的作品-------
--创建临时表
select * into #photo FROM (SELECT az_photo.id FROM az_photo left join az_FreeDownLog on az_photo.id=az_FreeDownLog.photoid
where filestatus=2 and downhit=0 and Addtime BETWEEN '20110101' AND '20171231' AND az_FreeDownLog.ID is null)photo
--确认数据量
SELECT count(*) FROM #photo
--执行更新
WHILE ( 1 = 1 )
BEGIN
UPDATE dbo.az_photo
SET filestatus=3,ModifiedOn=GETDATE()
WHERE id in ( SELECT top 1000 ID FROM #photo order by id desc)
IF @@ROWCOUNT < 1000
BEGIN
BREAK
END
ELSE
BEGIN
delete #photo where id in (SELECT top 1000 ID FROM #photo order by id desc)
END
WAITFOR DELAY '00:00:01'
END
--删除临时表
drop table #photo