请教递归更新问题,请大家帮忙!!

[zhangzs8896]

有一表TB,内容如下:
子 父
123 456
456 789
012 234

也就是说123的父节点是456,456的父节点789.012的父节点是234
我想得到这样的结果:

子 父
789 456
789 789
234 234


也就是把子列的值更改为最终父节点的值.
谢谢!

[CN_SQL]

2000还是2005？

[zhangzs8896]

2005

[zhangzs8896]

是SQL2005

[zhangzs8896]

[Quote=引用 1 楼 CN_SQL 的回复:]
2000还是2005？
[/Quote]
是SQL2005

[等不到来世]

with cte as
(
   select topid=pid,id,pid from tb a where not exists(select 1 from tb where id=a.pid)
   union all
   select c.topid,a.id,a.pid from tb a join cte c on a.pid=c.id
)
update tb
set id=cte.topid
from cte
where tb.pid=cte.pid

[等不到来世]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(3),[pid] int)
insert [tb]
select '123',456 union all
select '456',789 union all
select '012',234
go

with cte as
(
   select topid=pid,id,pid from tb a where not exists(select 1 from tb where id=a.pid)
   union all
   select c.topid,a.id,a.pid from tb a join cte c on a.pid=c.id
)
update tb
set id=cte.topid
from cte
where tb.pid=cte.pid

select * from [tb]
--测试结果:
/*
id   pid
---- -----------
789  456
789  789
234  234

(3 行受影响)
*/

[等不到来世]

pid类型换一下，无大碍

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(3),[pid] varchar(3))
insert [tb]
select '123','456' union all
select '456','789' union all
select '012','234'
go

with cte as
(
   select topid=pid,id,pid from tb a where not exists(select 1 from tb where id=a.pid)
   union all
   select c.topid,a.id,a.pid from tb a join cte c on a.pid=c.id
)
update tb
set id=cte.topid
from cte
where tb.pid=cte.pid

select * from [tb]
--测试结果:
/*
id   pid
---- -----------
789  456
789  789
234  234

(3 行受影响)
*/

[让你望见影子的墙]

如果嵌套的层次不多的话，可以使用连接，直接写

update a
set a.id=case when b.id is null then a.pid else b.pid end
from tb a left join tb b on b.pid=a.id

select * from tb

456	456
456	789
234	234