Last active
February 11, 2020 15:35
-
-
Save tharikaGitHub/4d9b4c39794c6b78079d761d382398f3 to your computer and use it in GitHub Desktop.
REG_TAG and REG_RESOURCE_TAG Table Cleanup Scripts Type 1
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 CONSTRAINT 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment