Cannot insert duplicate key in object (GetReparentedValue / hierarchyid)

Using examples I found on the web I have created a function which reparents children using the GetReparentedValue.

However when I have ran the code I get the following error: Cannot insert duplicate key in object.

I understand why (because I am trying to reparent the children and the new parent already has children so I need to know the MAX path (hierarchyid) of the child wit开发者_Go百科hin the new parent structure, but I don't understand how I'm actually going to do that.

path 0x58

oldPath 0x

new path 0x68

SqlCommand command = new SqlCommand("UPDATE Structure SET " +
                                    "Path = " + path + ".GetReparentedValue" +
                                    "(" +
                                      oldPath + ", " + newPath +
                                    ")" +
                                    "ParentID = @id " +
                                    "WHERE Path = " + path, _connection);

I have to do this when adding a child so I thought it would need to add this somewhere to the query above but I dont know where path + ".GetDescendant(" + lastChildPath + ", NULL)

Database Table

StructureID   int                         Unchecked  
Path          hierarchyid                 Unchecked  
PathLevel     ([Path].[GetLevel]())       Checked  
Description   nvarchar(50)                Checked  
ParentID      int                         Checked  
ParentPath    ([Path].[GetAncestor]((1))) Checked  

Anyone have any suggestion?

Thanks in advance for any help :-)

Clare

There are a couple of changes you can make to get this to work. First, you don't need the oldPath that represents the parent of the node that you want to move. In the .GetReparentedValue function, you put the hierarchyid of the node that is moving, which is the value in path.

The second change is to add another SELECT statement to apply your GetDescendant function. Here's a sample script that you can try in SQL Server Management Studio (SSMS), or alter to incorporate into your SQLCommand calls. The first few lines (variable declarations are assignments) are only for running in SSMS. You would transfer the last SELECT and the UPDATE statements to the calling code.

DECLARE @Path hierarchyid
DECLARE @oldPath hierarchyid
DECLARE @newPath hierarchyid
SELECT @Path=0x58, @oldPath=0x, @newPath=0x68

SELECT @newPath = @newPath.GetDescendant(MAX(Path), NULL)
FROM Structure
WHERE path.GetAncestor(1)=@newPath;

UPDATE Structure
SET Path = Path.GetReparentedValue(@Path, @newPath)
WHERE Path = @Path;

Your UPDATE statement and this revision will only re-parent a single node. It will not automatically move the children of the moving node. Children of the moving-node will be orphaned.

If you need to move the selected node and all descendants of the node, you can use the following variation of the previous statements.

DECLARE @Path hierarchyid
DECLARE @oldPath hierarchyid
DECLARE @newPath hierarchyid
SELECT @Path=0x58, @oldPath=0x, @newPath=0x68

SELECT @newPath = @newPath.GetDescendant(MAX(Path), NULL)
FROM Structure
WHERE Path.GetAncestor(1) = @newPath ;

UPDATE Structure
SET Path = Path.GetReparentedValue(@Path, @newPath)
WHERE Path.IsDescendantOf(@Path) = 1;

Actually, the only change from the first script to this script is in the very last line. The Path.IsDescendantOf(@Path) = 1 test is true for all descendants of @Path, including @Path. The hierarchical relationships will be maintained after the update.

This is another example of moving a subtree and all of it's children. It is essentially the same as the accepted answer. This is taken from the Docs:

CREATE PROCEDURE MoveOrg(@oldMgr nvarchar(256), @newMgr nvarchar(256) )  
AS  
BEGIN  
DECLARE @nold hierarchyid, @nnew hierarchyid  
SELECT @nold = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @oldMgr ;  

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  
BEGIN TRANSACTION  
SELECT @nnew = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @newMgr ;  

SELECT @nnew = @nnew.GetDescendant(max(OrgNode), NULL)   
FROM HumanResources.EmployeeDemo WHERE OrgNode.GetAncestor(1)=@nnew ;  

UPDATE HumanResources.EmployeeDemo    
SET OrgNode = OrgNode.GetReparentedValue(@nold, @nnew)  
WHERE OrgNode.IsDescendantOf(@nold) = 1 ;  

COMMIT TRANSACTION  
END ;  
GO