Delete Duplicate Records – Leave Only 1 Behind MS SQL
Recently I’ve had to delete duplicate records from the Record Link table in Microsoft Dynamics Nav.
Using the following SQL, (which uses the OVER clause for partitioning) this deletes all duplicate records, leaving the first unique item behind.
The SQL can be updated easily to function for other tables / scenarios. Currently it uses the following fields to identify duplicates:
- URL 1
The SQL does not look at the Notes, which is stored as a BLOB.
DELETE FROM [Record Link] WHERE [Link ID] IN ( SELECT [Link ID] FROM ( SELECT * ,ROW_NUMBER() OVER ( PARTITION BY [Record ID] ,[Description] ,[URL1] ORDER BY [Link ID] ) AS [ItemNumber] FROM [Record Link] WHERE [Note] IS NULL ) a WHERE ItemNumber > 1 )