Checking SQL script to Delete Water Right Information

[rwjam]

Here is an example the SQL code I've been using to delete records from the water rights data. I'd either run this entire script or in sections, depending on what I'm trying to accomplish. The goal would be to remove water allocations, sites, water source, and related bridge information from the database. I would do this in the event I've made some major changes to the logic we use to prepare the data for database upload / entry. This is an example of how I would prepare to remove water rights data for say Utah (UT).

In some cases I try not to remove the existing data and just simply try to upload new data to override the old. This doesn't always work as it seems our data uploader script expects a clean upload everytime.

• Does this look right to you?
• Had the comment (02/25/2022) of deleting from the WaterSources_dim table taking a very long time. Checking to see if this is the source of that long issue.

--- 1 delete AllocationAmounts_fact
DELETE FROM Core.AllocationAmounts_fact where OrganizationID = 37;

--- 2 delete Sites_dim
DELETE FROM Core.Sites_dim WHERE SiteUUID LIKE 'UTwr_S%';

--- 3 delete WaterSources_dim
DELETE FROM Core.WaterSources_dim WHERE WaterSourceUUID LIKE 'UTwr_WS%';

--- 4 delete related bridge table info
-- ===========================
-- Core.PODSite_POUSite_fact
-- ===========================
DELETE FROM Core.PODSite_POUSite_fact
WHERE PODSiteID NOT IN (SELECT f.SiteID FROM Core.Sites_dim f);
DELETE FROM Core.PODSite_POUSite_fact
WHERE POUSiteID NOT IN (SELECT f.SiteID FROM Core.Sites_dim f);


-- ===========================
-- Core.WaterSourceBridge_Sites_fact
-- ===========================
DELETE FROM Core.WaterSourceBridge_Sites_fact
WHERE WaterSourceID NOT IN (SELECT f.WaterSourceID FROM Core.WaterSources_dim f);
DELETE FROM Core.WaterSourceBridge_Sites_fact
WHERE SiteID NOT IN (SELECT e.SiteID FROM Core.Sites_dim e);


-- ===========================
-- Core.AllocationBridge_Sites_fact
-- ===========================
-- Delete Unused PODs
DELETE FROM Core.AllocationBridge_Sites_fact
WHERE (AllocationAmountID NOT IN (SELECT f.AllocationAmountID FROM Core.AllocationAmounts_fact f))
	AND (SiteID NOT IN (SELECT e.SiteID FROM Core.Sites_dim e));


-- ===========================
-- Core.AllocationBridge_BeneficialUses_fact
-- ===========================
DELETE FROM Core.AllocationBridge_BeneficialUses_fact
WHERE AllocationAmountID NOT IN (SELECT f.AllocationAmountID FROM Core.AllocationAmounts_fact f);