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
Solved

T-SQL recursive delete

Posted on 2013-01-15
4
307 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
  • 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 39

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 39

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl Query to find x consecutive Nbrs in a Table 30 97
SQL Stored Proc - Performance Enhancement 15 54
SQL query and VBA 5 45
Nested Case statement 4 37
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

860 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