As shown, you need to update the path value of the table in the path field, such as id=14: -1,id=17 's Path value: -1.14,id=20 's Path value: -1.14.18.19
Step 1, create a function
Create FUNCTION [dbo].[f_org]( @id int )RETURNS TABLE asRETURN ( withtesttable as(SelectUpperid fromCore_deptinfowhereId=@id Union All SelectCore_deptinfo.upperid fromcore_deptinfo,testtablewhereCore_deptinfo.id=testtable. Upperid)SelectUpperid fromtesttable)
Step 2, loop execution sql
Declare @minId int Declare @maxId int Declare @result nvarchar( -)Declare @count intSelect @minId = MIN(ID) fromCore_deptinfoSelect @maxId = MAX(ID) fromCore_deptinfo while @maxId >=@minIdBEGIN Select @count = COUNT(1) fromCore_deptinfowhereId=@maxId; if @count=1 begin Select @result =(Select CONVERT(nvarchar( -), Upperid)+'.' fromDbo. F_org (@maxId)Order byUpperid forXML Path ("') ) Set @result = Left(@result,LEN(@result)-1) UpdateCore_deptinfoextendSetPath=@result whereId=@maxId End SET @maxId = @maxId -1END