Loop a delete query in a stored procedure
I have the following query in a stored procedure
DELETE FROM Account_T
WHERE group_item_flag = 'G' AND child NOT IN
(SELECT DISTINCT parent
FROM Account_T)
This is executed on a recursive table.
Now the issue is that there can be up to 20 levels in the tree, so Each time I run it, I potentially create new members that can be deleted if I run it again. So in order to ensure I delete everything that needs to be deleted, I run the same query 20 times. Right now I am doing that by copying and pasting the same query 20 times. I was wondering if there was a more graceful way to do it using a LOOP?
If anyone is wondering, the reason the query is run is to delete parent members in the tree, that have no children.
TIA
DELETE FROM Account_T
WHERE group_item_flag = 'G' AND child NOT IN
(SELECT DISTINCT parent
FROM Account_T)
This is executed on a recursive table.
Now the issue is that there can be up to 20 levels in the tree, so Each time I run it, I potentially create new members that can be deleted if I run it again. So in order to ensure I delete everything that needs to be deleted, I run the same query 20 times. Right now I am doing that by copying and pasting the same query 20 times. I was wondering if there was a more graceful way to do it using a LOOP?
If anyone is wondering, the reason the query is run is to delete parent members in the tree, that have no children.
TIA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I gotcha...the way I had it would just keep running until there was ntohing left to delete. If you ONLY want it to run 20 times:
declare @x int
set @x = 20
while @x > 0
BEGIN
DELETE FROM Account_T
WHERE group_item_flag = 'G' AND child NOT IN
(SELECT DISTINCT parent
FROM Account_T)
SET @x = @x - 1
END
declare @x int
set @x = 20
while @x > 0
BEGIN
DELETE FROM Account_T
WHERE group_item_flag = 'G' AND child NOT IN
(SELECT DISTINCT parent
FROM Account_T)
SET @x = @x - 1
END
ASKER
Well, now you got me thinking, how will it know when there is nothing left to delete? Your first way might be more efficient because it often doesn't need to go the full 20 times, I just do that to be safe.
Are you basically saying that when it runs the query and returns "0row(s) affected" it will know to stop?
Are you basically saying that when it runs the query and returns "0row(s) affected" it will know to stop?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
note that the first version is "better", as it will indeed only run as many times as the DELETE does something. after a DELETE with 0 items to delete, it stops.
and that cannot run "forever", as by simple recursive deduction:
say your table has x rows. the delete deletes either
* 0 rows (happens also if there are 0 rows in the table) => recursion stops
* 1 (or more, say y > 0) rows => run the same logic another time, with x-y rows (which is smaller than x) => finite number of loops
and that cannot run "forever", as by simple recursive deduction:
say your table has x rows. the delete deletes either
* 0 rows (happens also if there are 0 rows in the table) => recursion stops
* 1 (or more, say y > 0) rows => run the same logic another time, with x-y rows (which is smaller than x) => finite number of loops
Yes, it will delete so long as there are records to delete. once it hasn't deleted anymore, it is done.
ASKER
Thanks
ASKER
I don't want it to just continue running.
Thanks,