sql-server - 在 SQL 层次结构数据中移动父 AND 子项

Question

我们的数据库中有一个使用 hierarchyid 数据类型的表，我正试图找到一种方法将父级及其所有子级重新分配给新的父级。我找到了只移动 child 的方法，但 parent 没有被重新分配。我的 table 看起来像这样:

LocationOrg
[OrgNode] [hierarchyid] NOT NULL,
[OrgLevel]  AS ([OrgNode].[GetLevel]()),
[LocationID] [int] NOT NULL,
[LocationName] [varchar](20) NOT NULL

还有一些数据:

LocationID  | LocationName
__________  |_____________
1           | Headquaters
2           | Location1
3           | Location2
4           | Location3
5           | Location4
6           | Location5
7           | Location6
8           | Location7

现在(手动)设置 Location1 和 Location4 是 Headquarters 的子级，Location2 和 Location3 是 Location1 的子级，而 Location5、Location6 和 Location7 是 Location2 的子级:

--Headquaters
  --Location1
    --Location2
    --Location3
  --Location4
    --Location5
    --Location6
    --Location7

我如何编写可以重新分配父项及其子项的内容？例如，如果我想将 Location4 及其所有子项移动到 Location3 下:

--Headquaters
  --Location1
    --Location2
    --Location3
      --Location4
      --Location5
      --Location6
      --Location7

请注意位置 4 中的所有子项如何成为位置 3 的子项...我不想移动整个父/子结构，我需要将它们重新分配给新的父项。

这是我到目前为止所得到的，但同样，它只会移动 child 而不是它的 parent :

ALTER PROC [dbo].[ReorderLocationOrg]
    @LocationID int,
    @NewParentLocationID int
AS
BEGIN

    DECLARE @OldParent hierarchyid, 
        @NewParent hierarchyid

    SELECT @OldParent = OrgNode 
    FROM LocationOrg
    WHERE LocationID = @LocationID;

    SELECT @NewParent = OrgNode 
    FROM LocationOrg
    WHERE LocationID = @NewParentLocationID;

    DECLARE children_cursor CURSOR FOR
        SELECT OrgNode 
        FROM LocationOrg
        WHERE OrgNode.GetAncestor(1) = @OldParent;

        DECLARE @ChildId hierarchyid;
        OPEN children_cursor
        FETCH NEXT FROM children_cursor INTO @ChildId;
        WHILE @@FETCH_STATUS = 0
        BEGIN
        START:
            DECLARE @NewId hierarchyid;
            SELECT @NewId = @NewParent.GetDescendant(MAX(OrgNode), NULL)
            FROM LocationOrg 
            WHERE OrgNode.GetAncestor(1) = @NewParent;

            UPDATE LocationOrg
            SET OrgNode = OrgNode.GetReparentedValue(@ChildId, @NewId)
            WHERE OrgNode.IsDescendantOf(@ChildId) = 1;
            IF @@error <> 0 GOTO START -- On error, retry
                FETCH NEXT FROM children_cursor INTO @ChildId;

        END
    CLOSE children_cursor;
    DEALLOCATE children_cursor;

    --Move Parent to new node.
    --UPDATE LocationOrg
    --SET OrgNode = @OldParent.GetReparentedValue(@OldParent2, @NewParent)
    --WHERE OrgNode = @OldParent
END

如您所见(注释掉的部分)，我尝试将父项移动到光标之后，但它抛出一个错误，指出无法插入重复记录。

我在 Moving Subtrees 上看过这个链接但是该过程在保持其结构的同时移动父子关系，因此在我的情况下，Location4 将移动到 Location3 下，但位置 5、6 和 7 仍将是 Location4 的子级，而不是 3 ......这是我不知道的'想要。

感谢任何帮助!

Answer 1

重复错误很可能是由于您将旧父节点移动到新父节点下已占用的位置造成的。

试试这个来移动 sp 中的旧父节点(在光标之后)

--Get a new Hierarchical id for you record
DECLARE @NewOldParentId hierarchyid
SELECT @NewOldParentId = @NewParent.GetDescendant(MAX(OrgNode), NULL)
FROM LocationOrg 
WHERE OrgNode.GetAncestor(1) = @NewParent;

--Give this new id to the oldParent
UPDATE LocationOrg
SET OrgNode = @NewOldParentId
WHERE LocationID = @LocationID;