How to identify the duplicate records and delete those records from the table.

In this article, we will be discussing how to delete duplicate records from the table. Here we are using
ROW_NUMBER()  function.

ROW_NUMBER() OVER(PARTITION BY [] ORDER BY [] )

Creating Table :

CREATE TABLE DELETEDUPLICATERECORD  (ID INT,NAME VARCHAR(250),MAIL VARCHAR(250), 
     ADDRESS VARCHAR(250),PINDODE INT ) 


Inserting Sample Data:

INSERT INTO DELETEDUPLICATERECORD 
VALUES (1,'Ganesh','Ganesh@gmail.com', 'Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES (2,'Mohan','Mohan@gmail.com','Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (3, 'Raju','Raju@gmail.com','Hyd', '500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (4,'Kiran','Kiran@gmail.com','Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (5,'Charan','Charan@gmail.com','Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (6,'Ganesh','Ganesh@gmail.com','Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (7,'Mohan','Mohan@gmail.com','Hyd','500000') 

Select table:

SELECT * FROM   DELETEDUPLICATERECORD 










in the above image, we can see the table 'DELETEDUPLICATERECORD' having two duplicate records.

Now, let us identify those two records and delete them.

Query to identify duplicate  records:

SELECT *, 
       ROW_NUMBER() 
         OVER ( 
           partition BY NAME, MAIL 
           ORDER BY ID ) DuplicateCount 
FROM   DELETEDUPLICATERECORD 

OutPut:

In the above image, DuplicateCount column tells us how many duplicate records are there


Now filtering only duplicate records.

SELECT * 
FROM   (SELECT *, 
               ROW_NUMBER() 
                 OVER ( 
                   partition BY NAME, MAIL 
                   ORDER BY ID ) DuplicateCount 
        FROM   DELETEDUPLICATERECORD) T 
WHERE  DuplicateCount >= 2 


OutPut:

Deleting the records.


DELETE T 
FROM   (SELECT *, 
               ROW_NUMBER() 
                 OVER ( 
                   partition BY NAME, MAIL 
                   ORDER BY ID ) DuplicateCount 
        FROM   DELETEDUPLICATERECORD) T 
WHERE  DuplicateCount >= 2 

Final Results after deleting duplicate records.


SELECT * FROM   DELETEDUPLICATERECORD 

Thank you for visiting the Blog, hope you got the required information.