CODEWITHSUNDEEP
sql-server-2008recursionsql-updatecommon-table-expression
mpaul
mpaul

Reputation: 157

SQL Server update within recursive query

I am using Microsoft SQL Server 2008 and trying to accomplish something very similar to this post using a recursive query: Recursive Child/Parent queries in T/SQL

Naturally I've attempted to simplify things; hopefully I have done so in a clear manner. My table looks something like this:

Parent  Child
A       A
A       B
B       D
D       H
D       I
A       C
C       E
E       C
E       J
E       K
C       F
C       G

Visually the relationships would look like this:

A
    B
        D
            H
            I
    C
        E
            C
            J
            K
        F
        G

I need to create a new column in the table that represents a child entity's path to the top level (A in this case):

Parent  Child  Chain
A       A      A
A       B      A,B
B       D      A,B,D
D       H      A,B,D,H
D       I      A,B,D,I
A       C      A,C
C       E      A,C,E
E       C      A,C,E,C
E       J      A,C,E,J
E       K      A,C,E,K
C       F      A,C,F
C       G      A,C,G

The wrinkle in this, and the reason parent-child may not be the terms to use here can be seen with entity C, which is both a parent and a child to entity E, which results in an endless recursive loop.

My thought is to update the chain value within the recursive loop and limit recursive calls to entities with a NULL value in the chain column. The idea is to recurse an entity only the first time it is encountered. What I have won't work and I'm not sure how to integrate the update into the recursive call:

WITH r(parent,child,mychain)
AS
(
    SELECT parent, child, child AS myChain 
    FROM myTable 
    WHERE parent = 'A' AND
    parent <> child AND
    chain IS NULL
    UNION ALL
    SELECT v.parent, v.child, myChain + ',' + v.child AS myChain 
    FROM myTable v
    INNER JOIN r ON r.child = v.parent
    WHERE v.parent <> v.child
)
UPDATE myTable
SET chain = r.myChain
FROM myTable AS c
JOIN r ON c.ID = r.ID

Any suggestions on how I can accomplish this?

Thanks in advance.

Edit:

It may be I've oversimplified a little. Because the actual values for the parent and child columns are quite long (in the neighborhood of 20+ characters) I really need to create a chain using the record ids as ooposed to the values. I've modified Simon's suggestion as shown below. This gets me pretty close; however, the C -> E record does not get output (all records should eventually get updated with the chain). It may not matter; I'm still trying to work through it.

with r as
(
    SELECT id, parent, child, 
      CAST( id AS VARCHAR(1024) ) AS CHAIN,
      0 as level
    FROM myTable 
    WHERE id = '1' 


    UNION ALL
    SELECT c.id, c.parent, c.child, 
      cast( (r.CHAIN + ',' + CAST( c.id AS varchar(10)) ) AS varchar(1024)) AS CHAIN,
      r.level + 1 as level
    FROM myTable c
    JOIN r
      ON r.child = c.parent
    WHERE c.parent != c.child AND r.parent != c.child

)
select * from r order by r.level, r.parent, r.child;

Upvotes: 2

Views: 4413

Answers (1)

Simon
Simon

Reputation: 2960

Try this:

with r as
(
    SELECT parent, child, 
      CAST( (parent + ',' + child) AS VARCHAR(10)) AS chain, 
      0 as level
    FROM myTable 
    WHERE parent = 'A' 
    AND parent != child

    UNION ALL
    SELECT c.parent, c.child, 
      cast((r.chain + ',' + c.child) as varchar(10)) AS chain, 
      r.level + 1 as level
    FROM myTable c
    JOIN r
      ON r.child = c.parent
    WHERE r.chain NOT LIKE '%,' + c.child + ',%'

)
select * 
from r 
order by r.level, r.parent, r.child;

Check out on: SQL Fiddle

Upvotes: 1

Related Questions