[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Loop a delete query in a stored procedure

Posted on 2009-04-16
7
Medium Priority
?
260 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:garycris
  • 3
  • 3
7 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 24157572
you coudl do something like this:

declare @x int
set @x = 1

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 = @@ROWCOUNT
END
0
 
LVL 5

Author Comment

by:garycris
ID: 24157604
Where do I define that I only want it to run 20 times?

I don't want it to just continue running.


Thanks,
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24157616
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
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 5

Author Comment

by:garycris
ID: 24157728
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?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24157741
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

 
 
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24157820
Yes, it will delete so long as there are records to delete.  once it hasn't deleted anymore, it is done.
0
 
LVL 5

Author Closing Comment

by:garycris
ID: 31570978
Thanks
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question