REG_TAG and REG_RESOURCE_TAG Table Cleanup Scripts Type 3

reg_tag_cleanup_type3.sql

CREATE TABLE TEMP_REG_RESOURCE_TAG_ID(REG_TAG_ID INTEGER);

-- Extract resource tag (ID) created when versioning is disabled --

INSERT INTO TEMP_REG_RESOURCE_TAG_ID(REG_TAG_ID)
   SELECT REG_TAG_ID
   FROM REG_RESOURCE_TAG
   WHERE REG_PATH_ID IN
       (SELECT REG_PATH_ID
        FROM REG_RESOURCE);

 -- Extract resource tag (ID) created when versioning is enabled --

INSERT INTO TEMP_REG_RESOURCE_TAG_ID(REG_TAG_ID)
   SELECT REG_TAG_ID
   FROM REG_RESOURCE_TAG
   WHERE REG_VERSION IN
       (SELECT REG_VERSION
        FROM REG_RESOURCE);

 -- delete all unwanted REG_RESOURCE_TAG entries --

DELETE
FROM REG_RESOURCE_TAG
WHERE REG_TAG_ID NOT IN
    (SELECT REG_TAG_ID
     FROM TEMP_REG_RESOURCE_TAG_ID);

 -- delete all unwanted REG_TAG entries --

DELETE
FROM REG_TAG
WHERE REG_ID NOT IN
    (SELECT REG_TAG_ID
     FROM TEMP_REG_RESOURCE_TAG_ID);

 -- drop temporary table --

DROP TABLE TEMP_REG_RESOURCE_TAG_ID;