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.
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
Now, we have to follow the below steps to complete the task-
Step 1: First we have to create a table having named “DETAILS”-
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.
SELECT EMPNAME,DEPT,CONTACTNO,CITY, COUNT(*) FROM EMPDETAIL GROUP BY EMPNAME,DEPT,CONTACTNO,CITY HAVING COUNT(*)>1
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: