hierarchyid, remove children from one node and assign to another node

Question

I have a user table, with the use of hierarchyid, I need to remove all Subordinate Users from User 1 and assign them to User 2.

SELECT 
    US.cd_usuario       
    ,(SELECT cd_usuario FROM USUARIO USR WHERE USR.NODE = US.NODE.GetAncestor(1)) AS CD_USUARIO_RESPONSAVEL
FROM 
    USUARIO

Select data:

| cd_usuario    | CD_USUARIO_RESPONSAVEL
1               null   
2               null   
3               1   
4               1

I need it to stay that way:

| cd_usuario    | CD_USUARIO_RESPONSAVEL
1               null   
2               null   
3               2   
4               2



    DECLARE @CD_USUARIO_OLD INT, @CD_USUARIO_NEW INT
    SET @CD_USUARIO_OLD = 1
    SET @CD_USUARIO_NEW = 2

    DECLARE @NODE_USUARIO_RESPONSAVEL hierarchyid
    DECLARE @NODE_USUARIO_OLD hierarchyid
    DECLARE @NODE_USUARIO_NEW hierarchyid

    SELECT @NODE_USUARIO_RESPONSAVEL = NODE.GetAncestor(1).ToString() FROM USUARIO WHERE cd_usuario = @CD_USUARIO_OLD
    SELECT @NODE_USUARIO_OLD = NODE FROM USUARIO WHERE cd_usuario = @CD_USUARIO_OLD
    SELECT @NODE_USUARIO_NEW = NODE FROM USUARIO WHERE cd_usuario = @CD_USUARIO_NEW

    SELECT @NODE_USUARIO_NEW = @NODE_USUARIO_NEW.GetDescendant(max(NODE), NULL)
    FROM USUARIO WHERE NODE.GetAncestor(1) = @NODE_USUARIO_NEW

    UPDATE USUARIO SET NODE = NODE.GetReparentedValue(@NODE_USUARIO_RESPONSAVEL, @NODE_USUARIO_NEW)
    WHERE NODE.IsDescendantOf(@NODE_USUARIO_OLD) = 1

Answer 1

I resolved it that way:

DECLARE @CD_USUARIO_OLD INT 
DECLARE @CD_USUARIO_NEW INT

SET @CD_USUARIO_OLD = 1
SET @CD_USUARIO_NEW = 2  

DECLARE @NODE_USUARIO_OLD hierarchyid
DECLARE @NODE_USUARIO_NEW hierarchyid

SELECT @NODE_USUARIO_OLD = NODE FROM USUARIO WHERE cd_usuario = @CD_USUARIO_OLD
SELECT @NODE_USUARIO_NEW = NODE WHERE cd_usuario = @CD_USUARIO_NEW

DECLARE children_cursor CURSOR FOR
SELECT NODE FROM USUARIO
WHERE NODE.GetAncestor(1) = @NODE_USUARIO_OLD;


DECLARE @ChildId hierarchyid;
OPEN children_cursor
FETCH NEXT FROM children_cursor INTO @ChildId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        START:
            DECLARE @NODE_NEW hierarchyid;
            SELECT @NODE_NEW =  @NODE_USUARIO_NEW.GetDescendant(MAX(NODE), NULL)
            FROM USUARIO WHERE NODE.GetAncestor(1) = @NODE_USUARIO_NEW;

            UPDATE USUARIO
            SET NODE = NODE.GetReparentedValue(@ChildId, @NODE_NEW)
            WHERE NODE.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;

Answer 2

Hello Gabriel for what I understood of your question I would not do the way you are executing, I would make a very different tsql but that executes what you want, now repairs I will present my solution:

IF OBJECT_ID('tempdb..#Utilizador') IS NOT NULL
    DROP TABLE #Utilizador

--Criar a tabela utilizador

CREATE TABLE dbo.#Utilizador
    (
      ID_Utilizador INT PRIMARY KEY CLUSTERED
                        NOT NULL ,
      ID_Responsavel_Utilizador INT NULL
    );

--Inserir alguns dados na tabela
INSERT  INTO [dbo].[#Utilizador]
        ( [ID_Utilizador], [ID_Responsavel_Utilizador] )
VALUES  ( 1, NULL ),
        ( 2, NULL ),
        ( 3, 1 ),
        ( 4, 1 )
           GO
--Primeiro select simula o teu cenario de SELECT
SELECT  *
FROM    #Utilizador AS U

 --O update actualizar segundo as tuas regras

UPDATE  U
SET     U.ID_Responsavel_Utilizador = 2
FROM    #Utilizador AS U
WHERE   u.ID_Utilizador IN ( SELECT U.ID_Utilizador
                             WHERE  U.ID_Responsavel_Utilizador = 1 )


--Segundo select apenas para confirmar que te devolve o resultado pretendido simples!!! não precisas de funções
SELECT  *
FROM    #Utilizador AS U

I hope I helped you.

There is also a simple and direct solution:

UPDATE  U
SET     U.ID_Responsavel_Utilizador = 2
FROM    #Utilizador AS U
WHERE   u.ID_Responsavel_Utilizador = 1