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

I have decided this 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;

Hello Gabriel, I would not do it the way you are doing, I would do a very different tsql but it will do what you want, now I'll 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 have helped you.

There is still the simple and straightforward solution:

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