Hi,
I did this stored procedure for my needs but maybe that can be useful for other members too.
I wanted to delete from all the trees the unused nodes. I’m only attaching the example for the Geography tree but it works the same way for the other trees.
DELIMITER $$
DROP PROCEDURE IF EXISTS cleanGeographyTree;
CREATE PROCEDURE cleanGeographyTree(INOUT p1 INT)
BEGIN
DECLARE done INT DEFAULT 0;
-- Loop rank ids
RANK1: LOOP
-- Check if p1 is less than 0
IF p1 < 1 THEN
LEAVE RANK1;
END IF;
-- Perform DELETE operation
DELETE g
FROM geography g
LEFT JOIN geography gchild ON gchild.ParentID = g.GeographyID
LEFT JOIN geography gaccepted ON gaccepted.GeographyID = g.AcceptedID
LEFT JOIN geography gsynonym ON gsynonym.AcceptedID = g.GeographyID
LEFT JOIN locality l ON l.GeographyID = g.GeographyID
WHERE g.RankID = p1
AND l.LocalityID IS NULL
AND gchild.GeographyID IS NULL
AND gaccepted.GeographyID IS NULL
AND gsynonym.GeographyID IS NULL;
-- Decrement p1
SET p1 = p1 - 1;
END LOOP RANK1;
END$$
DELIMITER ;
SET @startValue = 1000;
-- Call the procedure
CALL cleanGeographyTree(@startValue);
-- Optionally, check the final value of the variable
SELECT @startValue AS FinalValue;
(plus) button appears and you can use it to create that node.