Stored procedure to clean up a tree (removing unlinked nodes)

Documentation
1

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 < 0 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;
4 Likes
2

Very helpful! Thank you for posting!

3

I have no use for this myself, but very nice.