Deleting Duplicate Data from Table - Part 2

In my earlier post (Deleting Duplicate Data from Table - Part 1 ), we saw there could be different types for row being duplicated.  Just to keep the blog smaller I posted the queries to deal with one type of row duplication – Entire row being duplicated.

Second type of duplication is different than the first one, here only key columns being duplicated where other columns could have different values. If you want to remove the duplicate data in such scenario, that means you want to keep one non key column value for one row and loose the other one. There could be different logic to decide which one to keep and which one to delete…

NOTE: Here I have taken a decision to keep the first row and delete the latest entries and have written the queries accordingly. If your business logic need you to keep the latest one and delete the older ones you must change the queries accordingly. (scenario selected here is deals with US state data, where duplicate entries could be a negligence of the operator or importing same data multiple times… in scenario of Employee or Client data, you may keep the latest records as it could have updated address/contact number etc…)

Script to populate create and populate USStates table with sample data… (ID column is made as Identity) considered Abbreviation column as Key Column.

CREATE TABLE USStates (ID INT IDENTITY(1,1) PRIMARY KEY,

Abbreviation VARCHAR(5),

StateName VARCHAR(100))

GO

INSERT INTO USStates (Abbreviation,StateName)

VALUES ('AL', 'ALABAMA'),

('AK', 'ALASKA'),

('AS', 'AMERICAN SAMOA'),

('AZ', 'ARIZONA '),

('AR', 'ARKANSAS'),

('CA', 'CALIFORNIA '),

('CO', 'COLORADO '),

('CT', 'CONNECTICUT'),

('DE', 'DELAWARE'),

('DC', 'DISTRICT OF COLUMBIA'),

('FM', 'FEDERATED STATES OF MICRONESIA'),

('FL', 'FLORIDA'),

('GA', 'GEORGIA'),

('GU', 'GUAM '),

('HI', 'HAWAII'),

('ID', 'IDAHO'),

('IL', 'ILLINOIS'),

('IN', 'INDIANA'),

('IA', 'IOWA'),

('KS', 'KANSAS'),

('KY', 'KENTUCKY'),

('LA', 'LOUISIANA'),

('ME', 'MAINE'),

('MH', 'MARSHALL ISLANDS'),

('MD', 'MARYLAND'),

('MA', 'MASSACHUSETTS'),

('MI', 'MICHIGAN'),

('MN', 'MINNESOTA'),

('MS', 'MISSISSIPPI'),

('MO', 'MISSOURI'),

('MT', 'MONTANA'),

('NE', 'NEBRASKA'),

('NV', 'NEVADA'),

('NH', 'NEW HAMPSHIRE'),

('NJ', 'NEW JERSEY'),

('NM', 'NEW MEXICO'),

('NY', 'NEW YORK'),

('NC', 'NORTH CAROLINA'),

('ND', 'NORTH DAKOTA'),

('MP', 'NORTHERN MARIANA ISLANDS'),

('OH', 'OHIO'),

('OK', 'OKLAHOMA'),

('OR', 'OREGON'),

('PW', 'PALAU'),

('PA', 'PENNSYLVANIA'),

('PR', 'PUERTO RICO'),

('RI', 'RHODE ISLAND'),

('SC', 'SOUTH CAROLINA'),

('SD', 'SOUTH DAKOTA'),

('TN', 'TENNESSEE'),

('TX', 'TEXAS'),

('UT', 'UTAH'),

('VT', 'VERMONT'),

('VI', 'VIRGIN ISLANDS'),

('VA', 'VIRGINIA '),

('WA', 'WASHINGTON'),

('WV', 'WEST VIRGINIA'),

('WI', 'WISCONSIN'),

('WY', 'WYOMING')

GO 3 --DONT MISS THE NUMBER 3, THIS WILL EXECUTE THE BATCH 3 TIMES TO POPULATE DUPLICATE ENTRIES

Following are different approaches to delete duplicate data based on Key columns.

--USING CTE – preferred one

;WITH CTEUSStates AS (SELECT ROW_NUMBER() OVER(PARTITION BY Abbreviation ORDER BY ID ) RN

      FROM USStates)

DELETE CTEUSStates

WHERE RN>1

--USING CO-RELATED SUBQUERY 1

DELETE D

FROM USStates D

WHERE EXISTS (SELECT 1 FROM USStates O WHERE O.Abbreviation = D.Abbreviation AND O.ID < D.ID )

--USING CO-RELATED SUBQUERY 2

DELETE D

FROM USStates D

WHERE ID > (SELECT MIN(ID) FROM USStates O WHERE O.Abbreviation = D.Abbreviation )

--USING SUBQUERY

DELETE      USStates

WHERE ID NOT IN (SELECT MIN(O.ID) FROM USStates O GROUP BY Abbreviation)

NB : When there is referential integrity  and table is being referenced from other table and the ID of possibly duplicate row being referred by reference table, we cannot delete such rows… FK constraint prevent us from deleting such rows. In such case we need to replace the ID of those rows with the ID being retained. In our case I have written below update statements that replaces the ID being deleted with the ID being retained for the key.

In such scenario Update statements should be run prior to delete statements.

Script to create the reference table and populate with sample data

CREATE TABLE USStateProperties (ID INT IDENTITY(1,1), USStateID INT NOT NULL REFERENCES USStates(ID),

      Property VARCHAR(50),

      PropertyValue VARCHAR(100))

GO

INSERT INTO USStateProperties (USStateID,Property,PropertyValue)

VALUES(51, 'Capital', 'Austin'),

(110, 'Area', '268820 SqMiles'),

(169, 'NickName', 'Lone Star State'),

(35, 'Capital', 'Trenton'),

(94, 'Area', '8729 sqml'),

(153, 'HighestPoint', '1,803 ft  (550 m)')

Update statements to replace the ID being deleted with the ID being retained…

--UDPATE 1

UPDATE SP

      SET SP.USStateID = O.ID

FROM USStateProperties SP

INNER JOIN USStates S ON S.ID = SP.USStateID

INNER JOIN (SELECT Abbreviation,

                        MIN(ID) AS ID

                  FROM USStates

                  GROUP BY Abbreviation )O ON O.Abbreviation = S.Abbreviation

WHERE O.ID < S.ID

--UPDATE 2

UPDATE SP

      SET SP.USStateID = (SELECT MIN(O.ID) FROM USStates O WHERE O.Abbreviation = S.Abbreviation)

FROM USStateProperties SP

INNER JOIN USStates S ON S.ID = SP.USStateID

     

There are other ways to delete such type of duplicate data and update the reference table… I have just posted couple of for reference.