Delete Duplicate Records in SQL Server

Delete Duplicate Records in SQL Server

It’s quite often that we come up with a situation where we have to delete duplicate rows from a table. Duplicate entry may be inserted by mistake or sometimes because of (any) change in a business rule etc.
Below you can have a look at 12 different ways to delete duplicate rows from database tables in different situations. Table values may be uniquely identified with an Identity Column or maybe not. This article will demonstrate both scenarios.

Delete Duplicate Records Example 1

CREATE TABLE DelTable (ID INT, NAME VARCHAR(20), [Address] VARCHAR(50));
 
INSERT INTO DelTable VALUES(1,'AB','ABCD'),
(2,'PQ','PQRS'),
(1,'AB','ABCD'),
(4,'XY','XYZ');
 
SELECT * FROM DelTable;
 
SET ROWCOUNT 1
DELETE FROM DelTable 
WHERE NAME = 'AB' AND Address='ABCD' AND ID=1
SET ROWCOUNT 0
 
SELECT * FROM DelTable;

Delete Duplicate Records Example 2

CREATE TABLE DelTable1 (ID INT, NAME VARCHAR(20), [Address] VARCHAR(50));

INSERT INTO DelTable1 VALUES(1,'AB','ABCD'),
(2,'PQ','PQRS'),
(1,'AB','ABCD'),
(4,'XY','XYZ'),
(1,'AB','ABCD');

SELECT * FROM DelTable1;

SET NOCOUNT ON
SET ROWCOUNT 1
WHILE 1 = 1
BEGIN
DELETE FROM DelTable1 
WHERE ID IN(SELECT ID FROM DelTable1 GROUP BY ID HAVING COUNT(*) > 1)
IF @@Rowcount = 0 
BREAK ;
END
SET ROWCOUNT 0 

SELECT * FROM DelTable1;

Delete Duplicate Records Example 3

CREATE TABLE DelTable2 (ID INT, NAME VARCHAR(20), [Address] VARCHAR(50));

INSERT INTO DelTable2 VALUES(1,'AB','ABCD'),
(2,'PQ','PQRS'),
(1,'AB','ABCD'),
(4,'XY','XYZ'),
(1,'AB','ABCD');
 
SELECT * FROM DelTable2;

SELECT * INTO  #temporary 
FROM DelTable2 
GROUP BY ID,NAME,Address 
HAVING COUNT(*) > 1
 
-- delete all rows that are duplicated
 
DELETE FROM DelTable2
FROM DelTable2 d INNER JOIN #temporary du
ON du.ID = d.ID
 
-- insert one row for every duplicate set
 
INSERT INTO DelTable2(ID,NAME,Address)
SELECT * FROM #temporary
 
DROP TABLE #temporary

SELECT * FROM DelTable2;

Delete Duplicate Records Example 4

CREATE TABLE DelTable3 (ID INT, NAME VARCHAR(20), [Address] VARCHAR(50));

INSERT INTO DelTable3 VALUES(1,'AB','ABCD'),
(2,'PQ','PQRS'),
(1,'AB','ABCD'),
(4,'XY','XYZ'),
(1,'AB','ABCD');

SELECT * FROM DelTable3;
 
SELECT  DISTINCT * INTO temporaryTable
FROM DelTable3 

TRUNCATE TABLE DelTable3

DROP TABLE DelTable3
 
EXEC sp_rename 'temporaryTable', 'DelTable3'

SELECT * FROM DelTable3;

Delete Duplicate Records Example 5

CREATE  TABLE DelTable4 (Value VARCHAR(20));
 
INSERT INTO DelTable4 VALUES ('First')
INSERT INTO DelTable4 VALUES ('Second')
INSERT INTO DelTable4 VALUES ('Second')
INSERT INTO DelTable4 VALUES ('Third')
INSERT INTO DelTable4 VALUES ('Third') 

SELECT * FROM DelTable4;

DECLARE @value VARCHAR(20),
@previousValue VARCHAR(20),
@noOfRepVal INT
 
DECLARE cursorValue CURSOR FORWARD_ONLY 
 FOR SELECT  Value FROM  DelTable4  ORDER BY Value
 FOR UPDATE

OPEN cursorValue
 
FETCH NEXT FROM cursorValue INTO @value
 
WHILE @@FETCH_STATUS = 0
BEGIN 
     IF @previousValue = @value 
  SELECT @noOfRepVal=COUNT(Value) FROM DelTable4 WHERE Value=@value;
  
  IF(@noOfRepVal >1)
  BEGIN
         DELETE TOP(@noOfRepVal-1) FROM DelTable4 WHERE Value=@value;
        END
        
        SET @previousValue = @value

        FETCH NEXT FROM cursorValue INTO @value
END 

CLOSE cursorValue
 
DEALLOCATE cursorValue

SELECT * FROM DelTable4;

Delete Duplicate Records Example 6

CREATE  TABLE DelTable5 (Value VARCHAR(20));
 
INSERT INTO DelTable5 VALUES ('First')
INSERT INTO DelTable5 VALUES ('Second')
INSERT INTO DelTable5 VALUES ('Second')
INSERT INTO DelTable5 VALUES ('Third')
INSERT INTO DelTable5 VALUES ('Third') 

SELECT * FROM DelTable5;
 
WITH numbered
AS 
(SELECT Value, ROW_NUMBER() OVER (PARTITION BY Value ORDER BY Value ) AS sn
FROM    DelTable5)
DELETE  FROM numbered
WHERE sn > 1

SELECT * FROM DelTable5;

Delete Duplicate Records Example 7

CREATE  TABLE DelTable_T (Value VARCHAR(20));
 
INSERT INTO DelTable_T VALUES ('First')
INSERT INTO DelTable_T VALUES ('Second')
INSERT INTO DelTable_T VALUES ('Second')
INSERT INTO DelTable_T VALUES ('Third')
INSERT INTO DelTable_T VALUES ('Third') 

SELECT * FROM DelTable_T

SELECT DISTINCT * INTO  NewTable 
FROM DelTable_T 

DROP TABLE DelTable_T

SELECT * FROM NewTable

Delete Duplicate Records Example 8

CREATE TABLE DelTable6 (ID INT, NAME VARCHAR(20), [Address] VARCHAR(50));
 
INSERT INTO DelTable6 VALUES(1,'AB','ABCD'),
(2,'PQ','PQRS'),
(3,'AB','ABCD'),
(4,'XY','XYZ'),
(5,'AB','ABCD');

SELECT * FROM DelTable6;
  
DELETE FROM DelTable6 
WHERE ID NOT IN(SELECT MIN(ID)FROM DelTable6 GROUP BY NAME, Address);
 
SELECT * FROM DelTable6;

Delete Duplicate Records Example 9

CREATE  TABLE DelTable7 (ID INT IDENTITY(1, 1), Value VARCHAR(20));
 
INSERT INTO DelTable7 VALUES ('First')
INSERT INTO DelTable7 VALUES ('Second')
INSERT INTO DelTable7 VALUES ('Second')

Select * from DelTable7
 
WHILE 1 = 1
BEGIN
DELETE FROM DelTable7
WHERE ID IN(SELECT MAX(ID) FROM DelTable7 GROUP BY Value HAVING COUNT(*) > 1)
 
IF @@ROWCOUNT = 0 
 BREAK;
END

Select * from DelTable7

Delete Duplicate Records Example 10

CREATE TABLE DelTable8 (ID INT IDENTITY(1, 1), Value VARCHAR(20));

INSERT INTO DelTable8 VALUES ('First'),
('Second'),
('Second'),
('Third'),
('Third');

SELECT * FROM DelTable8;
 
DELETE FROM d 
FROM DelTable8 AS d 
INNER JOIN DelTable8 AS m
ON m.Value = d.Value AND d.ID < m.ID;
 
SELECT * FROM DelTable8;

Delete Duplicate Records Example 11

CREATE TABLE DelTable9 (ID INT IDENTITY(1, 1), Value VARCHAR(20));

INSERT INTO DelTable9 VALUES ('First'),
('Second'),
('Second'),
('Third'),
('Third');

SELECT * FROM DelTable9;

DELETE FROM d FROM DelTable9 d
INNER JOIN (SELECT Value FROM DelTable9 GROUP BY Value HAVING COUNT(*) > 1) t 
ON d.Value = t.Value
LEFT OUTER JOIN ( SELECT[ID] = MAX(ID) FROM DelTable9 GROUP BY Value HAVING COUNT(*) > 1 ) m
ON d.ID = m.ID 
WHERE m.ID IS NULL;
 
SELECT * FROM DelTable9;

Delete Duplicate Records Example 12

CREATE TABLE DelTable10 (ID INT, NAME VARCHAR(20));
 
INSERT INTO DelTable10 VALUES(1,'AB'),(2,'PQ'),(3,'AB'),(4,'XY'),(5,'AB');

SELECT * FROM DelTable10;

DECLARE @totalRecords INT,
@rowNumber INT,
@value VARCHAR(20),
@numberOfRecords INT

SELECT @totalRecords=COUNT(ID) FROM DelTable10;

SET @rowNumber=1;

WHILE @rowNumber < @totalRecords
BEGIN
 SELECT TOP(1) @value=NAME FROM DelTable10 WHERE ID=@rowNumber;
 SELECT @numberOfRecords=COUNT(ID) FROM DelTable10 WHERE NAME = @value;
 PRINT @numberOfRecords;
 IF(@numberOfRecords > 1)
 BEGIN
  SET @numberOfRecords =@numberOfRecords - 1;
  SET ROWCOUNT @numberOfRecords
  UPDATE DelTable10 SET NAME=NULL WHERE ID = @rowNumber;
  SET ROWCOUNT 0
 END
 SET @rowNumber = @rowNumber + 1;
END

DELETE FROM DelTable10 WHERE NAME IS NULL;

SELECT * FROM DelTable10;