Loop a delete query in a stored procedure
Posted on 2009-04-16
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
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.