monster.sql

# Move node (with level column)
SET @entity_id := 2;
SET @new_parent_id :=0;

SELECT level FROM path WHERE entity_id = @new_parent_id LIMIT 1 INTO @new_par_lvl;
SELECT level FROM path WHERE entity_id = @entity_id LIMIT 1 INTO @entity_lvl;

CREATE TEMPORARY TABLE temp_ch SELECT entity_id, level - @entity_lvl AS level FROM path WHERE ancestor_id = @entity_id;
CREATE TEMPORARY TABLE temp_par SELECT ancestor_id FROM path WHERE entity_id = @entity_id;

DELETE FROM path WHERE entity_id = @entity_id;
DELETE FROM path WHERE entity_id IN (SELECT entity_id FROM temp_ch) AND ancestor_id IN (SELECT ancestor_id FROM temp_par);

INSERT INTO path VALUES (@entity_id, @new_parent_id, @new_par_lvl + 1);
INSERT INTO path SELECT @entity_id, ancestor_id, @new_par_lvl + 1 FROM path WHERE entity_id = @new_parent_id;

INSERT INTO path SELECT entity_id, @new_parent_id, level + @new_par_lvl + 1 FROM temp_ch;
INSERT INTO path SELECT a.entity_id, b.ancestor_id, a.level + @new_par_lvl + 1 FROM temp_ch AS a, path AS b WHERE b.entity_id = @new_parent_id;

SET @diff := CAST( @new_par_lvl AS SIGNED ) - CAST( (@entity_lvl - 1) AS SIGNED );
UPDATE path SET level = level + @diff WHERE entity_id IN (SELECT entity_id FROM temp_ch) AND ancestor_id = @entity_id;
DROP TABLE temp_ch;
DROP TABLE temp_par;