reparenting hierarchyid values

Question

If I have the following rows set up in a SQL Server 2008 table:

Row        Hierarchy
1          1/
2          2/
3          1/1
4          1/2
5          2/1
6          2/2
7          2/1/1

Currently row 5 is parented to row 2. I want to reparent row 5 to row 1, so it would now become 1/3 instead of 2/1. That much I have working so far. I want row 7 (and any other rows down the chain) to remain parented to row 5, which means row 7 should be 1/3/1. What would be some efficient sql to ensure that the hierarchy ids of all children records down the chain get updated as well?

Answer 1

The silence is deafening!

Have you read this? Reordering Data in a Hierarchical Table Using Hierarchical Methods