求助一个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
但是发现只能更新一层，在执行一次后再更新一层
是不是由于整个更新是一个事务，到第三层时需要获取的第二层信息没有更新上去，所以值不变
求解决或替代方案··
[解决办法]
--生成测试数据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;
