Open In App

SQL Query to Delete Duplicate Rows

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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');
img1

 

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

img2

 

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:

img3

 

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:

img6

 

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:

 


Last Updated : 25 Apr, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads