recursion - SQL Server update within recursive query -

i using microsoft sql server 2008 , trying accomplish similar post using recursive query: recursive child/parent queries in t/sql

naturally i've attempted simplify things; have done in clear manner. table looks this:

parent  child       a       b b       d d       h d             c c       e e       c e       j e       k c       f c       g 

visually relationships this:

a     b         d             h                 c         e             c             j             k         f         g 

i need create new column in table represents child entity's path top level (a in case):

parent  child  chain            a       b      a,b b       d      a,b,d d       h      a,b,d,h d            a,b,d,i       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, , reason parent-child may not terms use here can seen entity c, both parent , child entity e, results in endless recursive loop.

my thought update chain value within recursive loop , limit recursive calls entities null value in chain column. idea recurse entity first time encountered. have won't work , i'm not sure how integrate update recursive call:

with r(parent,child,mychain) (     select parent, child, child mychain      mytable      parent = 'a' ,     parent <> child ,     chain null     union     select v.parent, v.child, mychain + ',' + v.child mychain      mytable v     inner join r on r.child = v.parent     v.parent <> v.child ) update mytable set chain = r.mychain mytable c join r on c.id = r.id 

any suggestions on how can accomplish this?

thanks in advance.

edit:

it may i've oversimplified little. because actual values parent , child columns quite long (in neighborhood of 20+ characters) need create chain using record ids ooposed values. i've modified simon's suggestion shown below. gets me pretty close; however, c -> e record not output (all records should updated chain). may not matter; i'm still trying work through it.

with r (     select id, parent, child,        cast( id varchar(1024) ) chain,       0 level     mytable      id = '1'        union     select c.id, c.parent, c.child,        cast( (r.chain + ',' + cast( c.id varchar(10)) ) varchar(1024)) chain,       r.level + 1 level     mytable c     join r       on r.child = c.parent     c.parent != c.child , r.parent != c.child  ) select * r order r.level, r.parent, r.child;  

try this:

with r (     select parent, child,        cast( (parent + ',' + child) varchar(10)) chain,        0 level     mytable      parent = 'a'      , parent != child      union     select c.parent, c.child,        cast((r.chain + ',' + c.child) varchar(10)) chain,        r.level + 1 level     mytable c     join r       on r.child = c.parent     r.chain not '%,' + c.child + ',%'  ) select *  r  order r.level, r.parent, r.child;  

check out on: sql fiddle