首页 > 代码库 > SQL批量更新具有树形结构表Path字段

SQL批量更新具有树形结构表Path字段

如上图所示,需要更新该表中Path字段,如ID=14的Path值:-1,ID=17的Path值:-1.14,ID=20的Path值:-1.14.18.19

步骤1、创建函数

Create FUNCTION  [dbo].[F_Org](    @id int )RETURNS TABLE ASRETURN (with testTable as(select UpperID from Core_DeptInfo  where ID=@id union all select Core_DeptInfo.UpperID from Core_DeptInfo,testTable where Core_DeptInfo.ID=testTable .UpperID) select UpperID  from testTable   )

步骤2、循环执行SQL

declare @minId int declare @maxId int declare @result nvarchar(50)declare @count intselect  @minId = MIN(id) from Core_DeptInfoselect  @maxId = MAX(id) from Core_DeptInfoWHILE @maxId >=@minIdBEGIN    select @count = COUNT(1) from Core_DeptInfo where  ID=@maxId;    if @count=1    begin        select @result = (select  CONVERT(nvarchar(50),UpperID) +.  from dbo.F_Org(@maxId) order by UpperID    for xml path(‘‘)  )          set @result = LEFT(@result,LEN(@result)-1)          update Core_DeptInfoExtend set Path=@result where ID=@maxId    end          SET @maxId = @maxId -1END