SQL recursive logic

Question

I have a situation where I need to configure existing client data to solve a problem when our application incorrectly updated identifiers in a table, when it should have been.

Here is the script. We have a parent table in which rows can be inserted that effectively replace existing rows; replacement can be recursive. We also have a child table in which there is a field pointing to the parent table. In existing data, the child table can point to rows that have been replaced, and I need to fix this. However, I cannot simply update each line to a replacement line, because this line could also be replaced, and I need the last line to be displayed.

I was trying to find a way to write a CTE that would accomplish this for me, but I am struggling to find a query that finds what I'm actually looking for. Here is an example of a table I'm working with; the “ShouldBe” column is what I would like my update request to end, taking into account the recursive replacement of some rows.

DECLARE @parent TABLE (SampleID int, SampleIDReplace int, GroupID char(1)) INSERT INTO @parent (SampleID, SampleIDReplace, GroupID) VALUES (1, -1, 'A'), (2, 1, 'A'), (3, -1, 'A'), (4, -1, 'A'), (5, 4, 'A'), (6, 5, 'A'), (7, -1, 'B'), (8, 7, 'B'), (9, 8, 'B') DECLARE @child TABLE (ChildID int, ParentID int) INSERT INTO @child (ChildID, ParentID) VALUES (1, 4), (2, 7), (3, 1), (4, 3) 

Desired results in the child table after applying the update script:

 ChildID ParentID ParentID_ShouldBe 1 4 6 (4 replaced by 5, 5 replaced by 6) 2 7 9 (7 replaced by 8, 8 replaced by 9) 3 1 2 (1 replaced by 2) 4 3 3 (unchanged, never replaced) 

Answer 1

Returns what you are looking for:

 with cte as ( select sampleid, sampleidreplace, 1 as num from @parent where sampleidreplace <> -1 union all select p.sampleid, cte.sampleidreplace, cte.num+1 from @parent p join cte on p.sampleidreplace = cte.sampleId ) select c.*, coalesce(p.sampleid, c.parentid) from @child c left outer join (select ROW_NUMBER() over (partition by sampleidreplace order by num desc) as seqnum, * from cte ) p on c.ParentID = p.SampleIDReplace and p.seqnum = 1 

The recursive part tracks each match (4 → 5, 4 → 6). The add number is "generation". We really want the latest generation. This is identified using the row_number() function, ordering num in descending order - hence p.seqnum = 1 .

Answer 2

Well, it took me a while, and there are probably better ways to do this, but here is one option.

 DECLARE @parent TABLE (SampleID int, SampleIDReplace int, GroupID char(1)) INSERT INTO @parent (SampleID, SampleIDReplace, GroupID) VALUES (1, -1, 'A'), (2, 1, 'A'), (3, -1, 'A'), (4, -1, 'A'), (5, 4, 'A'), (6, 5, 'A'), (7, -1, 'B'), (8, 7, 'B'), (9, 8, 'B') DECLARE @child TABLE (ChildID int, ParentID int) INSERT INTO @child (ChildID, ParentID) VALUES (1, 4), (2, 7), (3, 1), (4, 3) ;WITH RecursiveParent1 AS ( SELECT SampleIDReplace, SampleID, 1 RecursionLevel FROM @parent WHERE SampleIDReplace != -1 UNION ALL SELECT A.SampleIDReplace, B.SampleID, RecursionLevel + 1 FROM RecursiveParent1 A INNER JOIN @parent B ON A.SampleId = B.SampleIDReplace ),RecursiveParent2 AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY SampleIdReplace ORDER BY RecursionLevel DESC) RN FROM RecursiveParent1 ) SELECT A.ChildID, ISNULL(B.ParentID,A.ParentID) ParentID FROM @child A LEFT JOIN ( SELECT SampleIDReplace, SampleID ParentID FROM RecursiveParent2 WHERE RN = 1) B ON A.ParentID = B.SampleIDReplace OPTION(MAXRECURSION 500) 

Answer 3

I have an iterative SQL loop, which, it seems to me, looks like this:

 WHILE EXISTS (SELECT * FROM #child C INNER JOIN #parent P ON C.ParentID = P.SampleIDReplace WHERE P.SampleIDReplace > -1) BEGIN UPDATE #child SET ParentID = SampleID FROM #parent WHERE #child.ParentID = SampleIDReplace END 

Basically, the while condition compares the contents of the parent identifier column in the child table and sees if there is a corresponding value in the SampleIDReplace column of the parent table. If there is, it goes and gets the SampleID of this record. It stops only when the connection causes each SampleIDReplace to be -1, which means we have nothing more to do.

In the above examples, the above results lead to the expected result.

Please note that I had to use temporary tables instead of table variables so that the table is available in a loop. If you need to use table variables, then you need to do some more operations.

Obviously, if you have deep substitution hierarchies, you will make quite a few updates that can be taken into account when searching for a query to a production database.