Break statement does not work

I am trying to delete 500000 rows at a time from a big table. I am trying to
loop through it. I am attaching my code, what am I doing wrong. It either
will delete only 500000 rows and exit ot it will keep on looping. I am askin
g
it to delete data older than a certain date. If there are 3000000 rows , I
want it to loop till it deletes 3000000 rows and then exit. Instead if i set
the loop counter to 10, it will loop ten times and then exit. The break
statement does not work
here is the code
declare @.i int
select @.i=10
set rowcount 1000000
while @.i>0
begin
delete from flat_reporttbl
where logdate<'4/1/2004'
select @.i=@.i-1
break
end
thankscheck it.
delete top(10) from flat_reporttbl
where logdate<'4/1/2004'
the problem in ur code is ,
delete statement deletes all rows
which satisfies the condition in
where clause and
then check the while condition.
"batgirl" <batgirl@.discussions.microsoft.com> wrote in message
news:49793BEE-8D40-480E-8D5A-03E77A45A541@.microsoft.com...
>I am trying to delete 500000 rows at a time from a big table. I am trying
>to
> loop through it. I am attaching my code, what am I doing wrong. It either
> will delete only 500000 rows and exit ot it will keep on looping. I am
> asking
> it to delete data older than a certain date. If there are 3000000 rows , I
> want it to loop till it deletes 3000000 rows and then exit. Instead if i
> set
> the loop counter to 10, it will loop ten times and then exit. The break
> statement does not work
> here is the code
> declare @.i int
> select @.i=10
> set rowcount 1000000
> while @.i>0
> begin
> delete from flat_reporttbl
> where logdate<'4/1/2004'
> select @.i=@.i-1
> break
> end
> thanks|||I don't know if we can use the TOP keyword with delete. It gives me an error
message when I try to use it. Also I have already set the rowcount, so does
using TOP help?
batgirl
"batgirl" wrote:

> I am trying to delete 500000 rows at a time from a big table. I am trying
to
> loop through it. I am attaching my code, what am I doing wrong. It either
> will delete only 500000 rows and exit ot it will keep on looping. I am ask
ing
> it to delete data older than a certain date. If there are 3000000 rows , I
> want it to loop till it deletes 3000000 rows and then exit. Instead if i s
et
> the loop counter to 10, it will loop ten times and then exit. The break
> statement does not work
> here is the code
> declare @.i int
> select @.i=10
> set rowcount 1000000
> while @.i>0
> begin
> delete from flat_reporttbl
> where logdate<'4/1/2004'
> select @.i=@.i-1
> break
> end
> thanks