一个表中有1600万,用循环怎么写,每次删除100万,范围是在ID=1119577352~1134339198 --呵呵:别让楼主删除了多余的记录!
declare @a int
SET @a=1119577352;--大家都忽略了: 要考虑最后一次删除记录行时,不够1000000万条符合条件的记录
WHILE(@a<=1134339198+1000000)
BEGIN
delete from temp
where visitPKId >=@a and VistPKID <=(case WHEN (@a+1000000)> 1134339198 THEN 1134339198 ELSE @a+1000000 END);
SET @a=@a+1000001;
ENDhttp://topic.csdn.net/u/20091009/20/d3db593b-3f38-4b98-8e5e-c9b2874556be.html?24393
--考虑问题得仔细啊:不然,会给公司带来很大的损失的..........--呵呵................................
declare @a int
SET @a=1119577352;--大家都忽略了: 要考虑最后一次删除记录行时,不够1000000万条符合条件的记录
WHILE(@a<=1134339198+1000000)
BEGIN
delete from temp
where visitPKId >=@a and VistPKID <=(case WHEN (@a+1000000)> 1134339198 THEN 1134339198 ELSE @a+1000000 END);
SET @a=@a+1000001;
ENDhttp://topic.csdn.net/u/20091009/20/d3db593b-3f38-4b98-8e5e-c9b2874556be.html?24393
--考虑问题得仔细啊:不然,会给公司带来很大的损失的..........--呵呵................................
declare @a int
SET @a=1119577352;--大家都忽略了: 要考虑最后一次删除记录行时,不够1000000万条符合条件的记录
WHILE(@a<=1134339198+1000000)
BEGIN
delete from temp
where visitPKId >=@a and VistPKID <=(case WHEN (@a+1000000)> 1134339198 THEN 1134339198 ELSE @a+1000000 END);
SET @a=@a+1000001;
END
declare @a int
SET @a=1119577352;--大家都忽略了: 要考虑最后一次删除记录行时,不够1000000万条符合条件的记录
WHILE(@a<=1134339198)
BEGIN
delete from temp
where visitPKId >=@a and VistPKID <=(case WHEN (@a+1000000)> 1134339198 THEN 1134339198 ELSE @a+1000000 END);
SET @a=@a+1000001;
END
declare @a int
SET @a=1119577352;--大家都忽略了: 要考虑最后一次删除记录行时,不够1000000万条符合条件的记录
WHILE(@a<=1134339198)
BEGIN
delete from temp
where visitPKId >=@a and VistPKID <=(case WHEN (@a+1000000)> 1134339198 THEN 1134339198 ELSE @a+1000000 END);
SET @a=@a+1000001;
END
当 @a=1134577353时 符合while条件;
where visitPKId >=1134577353 and VistPKID <=(case WHEN (1135577353)> 1134339198 THEN 1134339198 ELSE 1134577353 END);
where visitPKId >=1134577353 and VistPKID <=(case WHEN (1135577353)> 1134339198 THEN 1134339198 ELSE 1134577353 END);
也就是 where visitPKId >=1134577353 and VistPKID <=1134339198
当 @a= 1133577353 符合while条件;
where visitPKId >=1133577353 and VistPKID <=(case WHEN (1133577353)> 1134339198 THEN 1134339198 ELSE 1133577353 END);
也就是 where visitPKId >=1134577353 and VistPKID <=1134339198
declare @n int,
@a int,
@b int
set @a = 1119577352
set @b = @a + 1000000
while(@b <=1134339198)
begin
delete from TEMP
where VisitPKId >=@a and VisitPKId <=@b
set @a = @b+1
set @b = @a + 1000000
if @b>1134339198
@b= 1134339198
end
delete from TEMP
--大牛角:劝你还是去做个测试吧,不明白我为什么要在while判断里面加1000000
--楼主:散分来!
--呵呵:别让楼主删除了多余的记录!
declare @a int
SET @a=1119577352;--大家都忽略了:要考虑最后一次删除记录行时,不够1000000万条符合条件的记录的情况!
WHILE(@a<=1134339198+1000000)
BEGIN
delete from temp
where visitPKId >=@a and VistPKID <=(case WHEN (@a+1000000)> 1134339198 THEN 1134339198 ELSE @a+1000000 END);
SET @a=@a+1000001;
END
declare @a int
SET @a=1119577352;--每次删除一百万
WHILE(@a<=1134339198)
BEGIN
delete from temp
where visitPKId >=1119577352 and VistPKID <=@a+1000000;
SET @a=@a+1000000;
END--最后一次删除记录行时,不够1000000万条符合条件的记录
delete from temp
where visitPKId >=1119577352 and VistPKID <=1134339198;
这样不也可以吗
另外就是打印出来结果再运行,而不要测试着运行,毕竟出错之后很麻烦的加上事务处理会更好
declare @a int
SET @a=1119577352;--每次删除一百万
WHILE(@a<=1134339198)
BEGIN
print 'delete from temp
where visitPKId >=1119577352 and VistPKID <='+ convert(varchar(20),@a+1000000);
SET @a=@a+1000000;
END--最后一次删除记录行时,不够1000000万条符合条件的记录
print 'delete from temp
where visitPKId >=1119577352 and VistPKID <=1134339198';然后复制出来检查是否有遗漏再运行