SQL Query to Delete Duplicate Rows

Through this article, we will learn how to delete duplicate rows from a database table. As we know that duplicity in our database tends to be a waste of memory space. It records inaccurate data and is also unable to fetch out the correct data from the database.

One or more rows that have identical or the same data value are considered to be Duplicate rows. 

Now, we have to follow the below steps to complete the task-

Step 1:  First we have to create a table having named “DETAILS”- 

Query:

CREATE TABLE DETAILS
( SN INT IDENTITY(1,1)
 EMPNAME VARCHAR(25),
 DEPT VARCHAR(20),
 CONTACTNO BIGINT NOT NULL,
 CITY VARCHAR(15)
 );
 

Step 2: Now, we have to insert values or data in the table.

INSERT INTO EMPDETAIL
 VALUES ('VISHAL','SALES',9193458625,'GAZIABAD'),
 ('VIPIN','MANAGER',7352158944,'BARIELLY'),
 ('ROHIT','IT',7830246946,'KANPUR'),
('RAHUL','MARKETING',9635688441,'MEERUT'),
('SANJAY','SALES',9149335694,'MORADABAD'),
('VIPIN','MANAGER',7352158944,'BARIELLY'),
('VISHAL','SALES',9193458625,'GAZIABAD'),
('AMAN','IT',78359941265,'RAMPUR');

Output: we have a view of the Table after inserting the values:

Step 3: In this step, we have to find how many rows are duplicated.

Query: 

SELECT EMPNAME,DEPT,CONTACTNO,CITY, COUNT(*) FROM EMPDETAIL
GROUP BY EMPNAME,DEPT,CONTACTNO,CITY
HAVING COUNT(*)>1

Output:

Step 4: You can also find out the unique row by using this row. 

SELECT EMPNAME,DEPT,CONTACTNO,CITY, COUNT(*) FROM DETAILS
GROUP BY EMPNAME,DEPT,CONTACTNO,CITY

Step 5: Finally we have to delete the duplicate row from the Database.

DELETE FROM DETAILS WHERE SN NOT IN (
SELECT MAX(SN) FROM DETAILS GROUP BY EMPNAME,DEPT,CONTACTNO,CITY)

Step 6: After deleting the duplicate row, then we have a view of the table: 

Output: