MoveTree MPTT

CREATE PROCEDURE MoveSubtree
    (IN my_root CHAR(2), IN new_parent CHAR(2))
LANGUAGE SQL
DETERMINISTIC
BEGIN ATOMIC
DECLARE origin_lft INTEGER;
DECLARE origin_rgt INTEGER;
DECLARE new_parent_rgt INTEGER;

SELECT lft, rgt
    INTO origin_lft, origin_rgt
    FROM Tree
    WHERE node = my_root;

SET new_parent_rgt
    = (SELECT rgt
        FROM Tree
        WHERE node = new_parent);
        
UPDATE Tree
    SET lft
        = lft
            + CASE
                WHEN new_parent_rgt < origin_lft
                THEN CASE
                    WHEN lft BETWEEN origin_lft AND origin_rgt
                    THEN new_parent_rgt - origin_lft
                    WHEN lft BETWEEN new_parent_rgt AND origin_lft - 1
                    THEN origin_rgt - origin_lft + 1
                    ELSE 0 END
                WHEN new parent_rgt > origin_rgt
                THEN CASE
                    WHEN lft BETWEEN origin_lft AND origin_rgt
                    THEN new_parent_rgt - origin_rgt - 1
                    WHEN lft BETWEEN origin_rgt + 1 AND new_parent_rgt - 1
                    THEN origin_lft - origin_rgt - 1
                    ELSE 0 END
                ELSE 0 END,
    rgt
        = rgt
            + CASE 
                WHEN new_parent_rgt < origin_lft
                THEN CASE
                    WHEN rgt BETWEEN origin_lft AND origin_rgt
                    THEN new_parent_rgt - origin_lft
                    WHEN rgt BETWEEN new_parent_rgt AND origin_lft - 1
                    THEN origin_rgt - origin_lft + 1
                    ELSE 0 END
                    WHEN new_parent_rgt > origin_rgt
                    THEN CASE
                        WHEN rgt BETWEEN origin_lft AND origin_rgt
                        THEN new_parent_rgt - origin_rgt - 1
                        WHEN rgt BETWEEN origin_rgt + 1 AND new_parent_rgt - 1
                        THEN origin_lft - origin_rgt - 1
                        ELSE 0 END
                    ELSE 0 END
END;