Solved

T-SQL recursive delete

Posted on 2013-01-15
4
310 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 500 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

726 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