SQL Query to Delete Duplicate Rows

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 the correct data from the database.

In this article, we will learn how to delete duplicate rows from a database table.One or more rows that have identical or the same data value are considered to be Duplicate rows.

There are a few steps from which we can create a DETAILS table and check how the deletion of duplicate rows is done.

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

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

Query:

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

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

Query:

INSERT INTO DETAILS (EMPNAME, DEPT, CONTACTNO, CITY)
VALUES 
    ('VISHAL', 'SALES', 9193458625, 'GAZIABAD'),
    ('VIPIN', 'MANAGER', 7352158944, 'BAREILLY'),
    ('ROHIT', 'IT', 7830246946, 'KANPUR'),
    ('RAHUL', 'MARKETING', 9635688441, 'MEERUT'),
    ('SANJAY', 'SALES', 9149335694, 'MORADABAD'),
    ('VIPIN', 'MANAGER', 7352158944, 'BAREILLY'),
    ('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 DETAILS
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

Output:

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

Query:

DELETE FROM DETAILS
WHERE SN NOT IN (
    SELECT MIN(SN)
    FROM DETAILS
    GROUP BY EMPNAME, DEPT, CONTACTNO, CITY
);
Select * FROM DETAILS;

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

Output: