Delete Duplicate Records in SQL

There are many ways to delete duplicate records/row in SQL. We are going to explain the query to delete the duplicate record in the table. Hare, we use Common table expression method So in this way we can maintain consistency and accuracy of data in the database.

For better understanding, let’s create a table called “info”

Create table info
(
    ID int,
    FirstName nvarchar(25),
    LastName nvarchar(25),
    Gender nvarchar(10),
    Location nvarchar(50),
    Email nvarchar(50),
    Income int
)
GO

 

Now, let’s insert some values with duplicate records.

Insert into info values (1, 'kishor', 'kumar', 'Male', 'Noida', '[email protected]', 60000)
Insert into info values (2, 'vikash', 'kumar', 'Male', 'Patna', '[email protected]', 40000)
Insert into info values (2, 'vikash', 'kumar', 'Male', 'Patna', '[email protected]', 40000)
Insert into info values (1, 'kishor', 'kumar', 'Male', 'Noida', '[email protected]', 60000)
Insert into info values (3, 'Pradeep', 'kumar', 'Male', 'Patna', '[email protected]', 20000)
Insert into info values (3, 'Pradeep', 'kumar', 'Male', 'Patna', '[email protected]', 20000)
Insert into info values (1, 'kishor', 'kumar', 'Male', 'Noida', '[email protected]', 60000)
Insert into info values (3, 'Pradeep', 'kumar', 'Male', 'Patna', '[email protected]', 20000)
Insert into info values (1, 'kishor', 'kumar', 'Male', 'Noida', '[email protected]', 60000)
Insert into info values (2, 'vikash', 'kumar', 'Male', 'Patna', '[email protected]', 40000)

 

 

 

 

 

 

 

 

 

 

 

 

Below Query will delete duplicate rows in SQL with accuracy and consistency of data.

WITH infoCTE AS
(
    SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER
    BY ID) AS RowNumber
    FROM info
)
DELETE FROM infoCTE WHERE RowNumber > 1

 

This query will delete all duplicate rows and leave the only unique record in the table. After delete, the output looks.

 

Leave a Reply

Your email address will not be published. Required fields are marked *