REG_TAG and REG_RESOURCE_TAG Table Cleanup Scripts Type 2

reg_tag_cleanup_type2.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);

-- Remove the foreign key constraint --

ALTER TABLE REG_RESOURCE_TAG DROP FOREIGN KEY REG_RESOURCE_TAG_FK_BY_TAG_ID;

 -- 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);

-- add the foreign key constraint back --

ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID);

 -- drop temporary table --

DROP TABLE TEMP_REG_RESOURCE_TAG_ID;