FOLLOW US

Saturday, November 3, 2012

Delete duplicates from SQL without having a primary key

Recently I had a problem with finding duplicate records in a SQL table without having a primary key. Because I didn't want to create a primary key in the table, I had to use a different method in my SQL statement.

In the SQL example below you will see how to find the duplicate records. I used the SQL
ROW_NUMBER() function for it to differentiate between duplicate records.

DELETE SubQuery FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) NumOccurrences
FROM [dbo].[ClarizenDeletions]) SubQuery
WHERE SubQuery.NumOccurrences > 1

No comments:

Post a Comment