title: SQL recursive logic
body: <p>I have a situation where I need to configure existing client data to address a problem where our application was not correctly updating IDs in a table when it should have been.</p>
  
  <p>Here's the scenario. We have a parent table, where rows can be inserted that effectively replace existing rows; the replacement can be recursive. We also have a child table, which has a field that points to the parent table. In existing data, the child table could be pointing at rows that have been replaced, and I need to correct that. I can't simply update each row to the replacing row, however, because <em>that</em> row could have been replaced as well, and I need the latest row to be reflected. </p>
  
  <p>I was trying to find a way to write a CTE that would accomplish this for me, but I'm struggling to find a query that finds what I'm actually looking for. Here's a sample of the tables that I'm working with; the 'ShouldBe' column is what I'd like my update query to end up with, taking into account the recursive replacement of some of the rows.</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Desired results in child table, after the update script has been applied:</p>
  
  <pre><code>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)
  </code></pre>