Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Finding and removing duplicate rows from database
-- SQL databate table dbo.Test with columns Id, FirstName, LastName, TransactionNumber, LastUpdatedDate
-- Select rows that are duplicates without using LastUpdatedDate
-- If needed can insert into a backup/archive table
SELECT t.Id, t.FirstName, t.LastName, t.TransactionNumber, t.LastUpdatedDate
FROM dbo.Test t
LEFT OUTER JOIN (
SELECT MIN(Id) as Id,
FirstName, LastName, TransactionNumber
FROM dbo.Test
GROUP BY FirstName, LastName, TransactionNumber
) as keepRows
ON t.Id = keepRows.Id
WHERE keepRows.Id IS NULL
-- Delete the duplicate rows
DELETE FROM dbo.Test
FROM dbo.Test t
LEFT OUTER JOIN (
SELECT MIN(Id) as Id,
FirstName, LastName, TransactionNumber
FROM dbo.Test
GROUP BY FirstName, LastName, TransactionNumber
) as keepRows
ON t.Id = keepRows.Id
WHERE keepRows.Id IS NULL
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment