Delete duplicate rows with no primary key on a SQL Server table

 

Problem

Every once in awhile a table gets created without a primary key and duplicate records get entered. The problem gets even worse when you have two identical rows in the table and there is no way to distinguish between the two rows. So how do you delete the duplicate record?

Solution

One option that SQL Server gives you is the ability to set ROWCOUNT which limits the numbers of records affected by a command. The default value is 0 which means all records, but this value can be set prior to running a command. So let's create a table and add 4 records with one duplicate record.

Create a table called duplicateTest and add 4 records.

CREATE TABLE dbo.duplicateTest 
( 
[ID] [int] , 
[FirstName] [varchar](25), 
[LastName] [varchar](25)  
) ON [PRIMARY] 

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith') 
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones') 
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White') 
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

If we select all data we get the following:

SELECT * FROM dbo.duplicateTest

ID	FirstName	LastName
1	Bob	Smith
2	Dave	Jones
3	Karen	White
1	Bob	Smith

If we try to select the record for Bob Smith will all of the available values such as the following query:

SELECT * FROM dbo.duplicateTest WHERE ID = 1 AND FirstName = 'Bob' AND LastName = 'Smith'

We still get 2 rows of data:

ID	FirstName	LastName
1	Bob	Smith
1	Bob	Smith

DELETE Duplicate Records Using ROWCOUNT

So to delete the duplicate record with SQL Server we can use the SET ROWCOUNT command to limit the number of rows affected by a query. By setting it to 1 we can just delete one of these rows in the table. Note: the select commands are just used to show the data prior and after the delete occurs.

SELECT * FROM dbo.duplicateTest 

DECLARE @id int = 1

IF EXISTS (SELECT count(*) FROM dbo.duplicateTest WHERE ID = @id HAVING count(*) > 1 )
BEGIN
   SET ROWCOUNT 1 
   DELETE FROM dbo.duplicateTest WHERE ID = @id 
   SET ROWCOUNT 0 
END

SELECT * FROM dbo.duplicateTest

Here is a note from Microsoft about using SET ROWCOUNT:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.

I tested the ROWCOUNT option with SQL Server 2017 and this option still works.

DELETE Duplicate Records Using TOP

With SQL Server 2005 and later we can also use the TOP command when we issue the delete, such as the following. Note: the select commands are just used to show the data prior and after the delete occurs.

-- delete all records and add records again
DELETE FROM dbo.duplicateTest
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith') 
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones') 
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White') 
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')	
	
SELECT * FROM dbo.duplicateTest 

DECLARE @id int = 1

IF EXISTS (SELECT count(*) FROM dbo.duplicateTest WHERE ID = @id HAVING count(*) > 1 )
   DELETE TOP(1) FROM dbo.duplicateTest WHERE ID = @id 

SELECT * FROM dbo.duplicateTest

So as you can see with SQL Server 2005 and later there are two options to allow you to delete duplicate identical rows of data in your tables.

DELETE Multiple Duplicate Records Using TOP

One of the downsides to the above approaches is that they only delete one record at a time. So if there are more than two duplicates you have to rerun the commands.

Here is another option submitted by one of our readers Basharat Bhat if there are more than two duplicates.

-- delete all records and add records again
DELETE FROM dbo.duplicateTest
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith') 
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones') 
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White') 
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')	
	
SELECT * FROM dbo.duplicateTest 

DECLARE @id int = 1

DELETE TOP (SELECT COUNT(*) -1 FROM dbo.duplicateTest WHERE ID = @id)  
FROM dbo.duplicateTest  
WHERE ID = @id

SELECT * FROM dbo.duplicateTest 

DELETE Multiple Duplicate Records Using CTE

Here is another option submitted by one of our readers. This approach checks all of the columns to make sure that each column is a duplicate versus just the ID column in the above examples. This will delete records when there are 2 or more duplicate rows.

DELETE FROM dbo.duplicateTest
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith') 
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones') 
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White') 
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')	

SELECT * FROM dbo.duplicateTest; 

with temp(rank1,id ,fname,lname)
as (
   select row_number() over ( partition by ID, FirstName, LastName order by ID, FirstName, LastName ) , * 
   from duplicateTest
)
delete from temp where rank1 > 1;

SELECT * FROM dbo.duplicateTest;

DELETE Multiple Duplicate Records Using %%lockres%%

Here is another option submitted by another one of our readers. This approach checks all of the columns to make sure that each column is a duplicate versus just the ID column in the above examples. This will delete records when there are 2 or more duplicate rows.

DELETE FROM dbo.duplicateTest
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith') 
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones') 
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White') 
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')	

SELECT * FROM dbo.duplicateTest; 

DELETE FROM a
FROM dbo.duplicateTest a
JOIN
(
SELECT MAX(%%lockres%%) pseudoID, id, FirstName, LastName
FROM dbo.duplicateTest
GROUP BY id, FirstName, LastName
) b ON b.id = a.id AND b.LastName = a.LastName AND b.FirstName = a.FirstName AND b.pseudoID <> a.%%lockres%%

SELECT * FROM dbo.duplicateTest;