Ask every master a SQL article

对不起谢谢

Table as:
id par root
1 0 A
twenty one      
3 1
4 0 ss
5 4 B
6 4
7 2 C
8 2

Use SQL to realize that the root value of par non-zero record is consistent with the root value of id equal to its par.

waxiaoning

Just execute it a few more times
update a set a.root=b.root from tablename a join tablename b on a.par=b.id

cv4bf5d65fd

update a
set a.root=b.root
from table A a, table A b
where a.par=b.id

waxiaoning

create function get_root(@id varchar(10))
returns varchar(10)
as
begin
   declare @str varchar(10)
   declare @i varchar(10)
   select @str=id,@i=par from tablename where id=@id
   while @i<>'0'
         select @str=id,@i=par from tablename where id=@i
   select @str=root from tablename where id=@str
  return(@str)
end
go
select dbo.get_root('7')
update tablename set root=dbo.get_root(id)