Updating “Hierarchyid” in SQL Server

Question

I've used Hierarchyid data type in one of my tables in SQL Server.

Now I want to change the father of one of the rows, but when I change that all its descendant HierarchyId's must change according to that.

Is there a function to do that or I must change all of them myself. If I need to do that, what is the best way ?

Thanks In advance

Answer 1

Use the "GetReparentedValue" Function to do it.

This is what I did for a table in my database :

DECLARE @FatherNode AS _INT_

SELECT @FatherNode = [PLC_PLC_ID]
FROM [dbo].[Place]
WHERE ([PLC_ID] = @PLC_ID)

UPDATE [dbo].[Place]
SET 
    [PLC_Tree] = [PLC_Tree].GetReparentedValue([dbo].[PLC_IDToTree](@FatherNode),[dbo].[PLC_IDToTree](@PLC_PLC_ID))
WHERE 
    ([PLC_Tree].IsDescendantOf([dbo].[PLC_IDToTree](@PLC_ID)) = 1) OR
    ([PLC_ID] = @PLC_ID)