Open In App
Related Articles

SQL Query to Delete Duplicate Rows

Improve Article
Improve
Save Article
Save
Like Article
Like

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:

 

Unlock the Power of Placement Preparation!
Feeling lost in OS, DBMS, CN, SQL, and DSA chaos? Our Complete Interview Preparation Course is the ultimate guide to conquer placements. Trusted by over 100,000+ geeks, this course is your roadmap to interview triumph.
Ready to dive in? Explore our Free Demo Content and join our Complete Interview Preparation course.

Last Updated : 25 Apr, 2023
Like Article
Save Article
Previous
Next
Similar Reads
Complete Tutorials