Removing duplicates

-- ============
-- setup of test environment
-- ============
CREATE TABLE #District(DistrictID INT PRIMARY KEY, 
                       DistrictName VARCHAR(50)
);

INSERT INTO #District(DistrictID, DistrictName)
SELECT 1, 'Cairo'
UNION SELECT 23, 'Cairo'
UNION SELECT 44, 'Cairo'
UNION SELECT 60, 'Alex'
UNION SELECT 62, 'Alex'
UNION SELECT 70, 'Aswan'
UNION SELECT 74, 'Aswan'
UNION SELECT 78, 'Aswan'
;

CREATE TABLE #Map(MapID INT PRIMARY KEY, 
                  DistrictID INT FOREIGN KEY REFERENCES #District(DistrictID), 
				  MapName VARCHAR(3)
);

INSERT INTO #Map
SELECT 1, 1, 'ABC'
UNION SELECT 2, 44, 'XYZ'
UNION SELECT 3, 23, 'DEF'
UNION SELECT 4, 60, 'GHI'
UNION SELECT 5, 62, 'JKL'
UNION SELECT 6, 70, 'MNO'
UNION SELECT 7, 74, 'PQR'
UNION SELECT 8, 78, 'STU'
;
-- ============

-- update Map table first to min DistrictID for a given DistrictName
UPDATE tto
SET tto.DistrictID = tfr.MIN_DistrictID
FROM #Map tto
JOIN (
   SELECT DistrictID
        , MIN(DistrictID) OVER(PARTITION BY DistrictName) MIN_DistrictID
   FROM #District
) tfr ON tfr.DistrictID = tto.DistrictID
-- verify updat of maps
SELECT * FROM #Map;

-- remove duplicates from districts based on min DistrictID for given DistrictName
;WITH districts AS (
   SELECT DistrictID
        , MIN(DistrictID) OVER(PARTITION BY DistrictName) MIN_DistrictID
   FROM #District
)
DELETE 
FROM districts 
WHERE DistrictID <> MIN_DistrictID
;
-- verify update of district list
SELECT * FROM #District;

-- ============
-- cleanup test environment
-- ============
DROP TABLE #Map;

DROP TABLE #District;
-- ============

Select allOpen in new window