求助一个update递归的问题
表结构是 id,t2,t3,parentID这样 表明 table
目的是要更新t2为他的父节点的t2+t3的值
我现在的写法是这样：
update t set t.t2=s.t2+t3 from table t left join table s on t.parentID=s.id
但是发现只能更新一层，在执行一次后再更新一层
是不是由于整个更新是一个事务，到第三层时需要获取的第二层信息没有更新上去，所以值不变

求解决或替代方案··

[解决办法]

SQL code

--生成测试数据create table tTmp( id varchar(10), t2 varchar(100), t3 varchar(100), parentid varchar(10))insert into tTmpselect '1', '12','13','0'union all select '2', '22','23','1'union all select '3', '32','33','2'select * from tTmp/*id  t2  t3  parentid1    12    13    02    22    23    13    32    33    2*/WITH cteTmp AS(    SELECT A.*, Convert(varchar(100),t2) as DetailName    FROM tTmp AS A    WHERE NOT EXISTS(SELECT * FROM tTmp WHERE id=A.parentid)    UNION ALL     SELECT A.*, Convert(varchar(100),(A.t2 + B.DetailName)) AS DetailName    FROM tTmp AS A    JOIN cteTmp AS B      ON A.parentid=B.id)SELECT id,t2,t3,parentid,DetailName+t3 as t2t3 FROM cteTmp order by DetailName;/*id  t2  t3  parentid  t2t31    12    13    0    12132    22    23    1    2212233    32    33    2    32221233*/select * from tTmp/*id  t2  t3  parentid1    12    13    02    22    23    13    32    33    2*/WITH cteTmp AS(    SELECT A.*, Convert(varchar(100),t2) as DetailName    FROM tTmp AS A    WHERE NOT EXISTS(SELECT * FROM tTmp WHERE id=A.parentid)    UNION ALL     SELECT A.*, Convert(varchar(100),(A.t2 + B.DetailName)) AS DetailName    FROM tTmp AS A    JOIN cteTmp AS B      ON A.parentid=B.id)update tTmp set tTmp.t2 = (select cteTmp.DetailName from cteTmp where tTmp.id = cteTmp.id)+ tTmp.t3select * from tTmp/*id  t2  t3  parentid1    1213    13    02    221223    23    13    32221233    33    2*/drop table tTmp;