REG_PROPERTY and REG_RESOURCE_PROPERTY Table Cleanup Scripts Type 3

reg_property_cleanup_type3.sql

CREATE TABLE TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID INTEGER);

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

INSERT INTO TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID)
   SELECT REG_PROPERTY_ID
   FROM REG_RESOURCE_PROPERTY
   WHERE REG_PATH_ID IN
       (SELECT REG_PATH_ID
        FROM REG_RESOURCE);

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

INSERT INTO TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID)
   SELECT REG_PROPERTY_ID
   FROM REG_RESOURCE_PROPERTY
   WHERE REG_VERSION IN
       (SELECT REG_VERSION
        FROM REG_RESOURCE);

 -- delete all unwanted REG_RESOURCE_PROPERTY entries --

DELETE
FROM REG_RESOURCE_PROPERTY
WHERE REG_PROPERTY_ID NOT IN
    (SELECT REG_PROPERTY_ID
     FROM TEMP_REG_RESOURCE_PROPERTY_ID);

 -- delete all unwanted REG_PROPERTY entries --

DELETE
FROM REG_PROPERTY
WHERE REG_ID NOT IN
    (SELECT REG_PROPERTY_ID
     FROM TEMP_REG_RESOURCE_PROPERTY_ID);

 -- drop temporary table --

DROP TABLE TEMP_REG_RESOURCE_PROPERTY_ID;