?
Solved

T-SQL recursive delete

Posted on 2013-01-15
4
Medium Priority
?
323 Views
Last Modified: 2013-01-15
I have the following code segment which works, kinda.  

DELETE FROM PSExtract.temp.RJZ_ENTITY_F_130107
FROM PSExtract.temp.RJZ_ENTITY_F_130107 AS K
WHERE K.[Direct Parent identifier] IN
      (
      SELECT K.[Direct Parent identifier]
      FROM PSExtract.temp.RJZ_ENTITY_F_130107 AS K
      LEFT OUTER JOIN PSExtract.temp.RJZ_ENTITY_F_130107 AS C
            ON K.[Direct Parent identifier] = C.[Data provider ID]
            WHERE C.[Data provider ID] IS NULL
      );

What is going on here is that a row has two keys in it.   The primary key ([Data provider ID]) which is the first column and then a parent key ([Direct Parent identifier]) later on in a different column.  All parent keys must be made valid by having a different row exist with the parent key as the primary key.  The above code goes through all the parent keys and tries to find the row for which that key is the primary key (thus validating the parent key).   If it cannot find a primary key row for a given parent key (i.e. the parent key is invalid), it deletes the row with that parent key.  That whole process works.

The problem: When a row is deleted, in some cases the deleted row "validated" the parent key on a different row which previously was valid but is now invalid.  I run the above code segment once, it works.   BUT I run it again and it again finds rows for which the parent key is invalid.  The reason they are invalid is because the rows which validated their parent keys were deleted in the first run.  I have to execute the code several times before I finally run out of rows to delete.   At that point i know that all parent keys are validated because all parent keys are can be found as primary keys on some other row.

Question:  Is there a way to code the above code segment so that it recursively executes until it runs out of rows to delete?   I tried to set this up but wasn't able to do so in any valid manner.  You will have to be specific with your answer as I'm pretty dense.

I'm running under MS SQLServer 2008.
0
Comment
Question by:RichNH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 1

Author Comment

by:RichNH
ID: 38779826
BTW, currently I'm getting around the problem by repeating the delete code 10X in the module.  Not very elegant but it gets me the data I need.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 38779957
You could fill in a int variable with @@ROWCOUNT (Returns the number of rows affected by the last statement) and if is NOT = 0 then LOOP
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 38779966
--something like:

declare @row_count int;
set @row_count = 1;

while @row_count > 0
begin
      DELETE FROM PSExtract.temp.RJZ_ENTITY_F_130107
      FROM PSExtract.temp.RJZ_ENTITY_F_130107 AS K
      WHERE K.[Direct Parent identifier] IN
              (
              SELECT K.[Direct Parent identifier]
              FROM PSExtract.temp.RJZ_ENTITY_F_130107 AS K
              LEFT OUTER JOIN PSExtract.temp.RJZ_ENTITY_F_130107 AS C
                        ON K.[Direct Parent identifier] = C.[Data provider ID]
                        WHERE C.[Data provider ID] IS NULL
              );
    set @row_count = @@ROWCOUNT;
end;
0
 
LVL 1

Author Closing Comment

by:RichNH
ID: 38780476
Man that is EXACTLY what I was looking for and works like a champ.   I've found it in the books I have too so obviously I have some reading to do.   A new area to explore.   tahnk you so much!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

770 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