Delete Duplicate Records in SQL

Delete Duplicate Records in SQL

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 the common table expression method So in this way we can maintain the 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', 'kishor@gmail.com', 60000)
Insert into info values (2, 'vikash', 'kumar', 'Male', 'Patna', 'vikash@gmail.com', 40000)
Insert into info values (2, 'vikash', 'kumar', 'Male', 'Patna', 'vikash@gmail.com', 40000)
Insert into info values (1, 'kishor', 'kumar', 'Male', 'Noida', 'kishor@gmail.com', 60000)
Insert into info values (3, 'Pradeep', 'kumar', 'Male', 'Patna', 'pradeep@gmail.com', 20000)
Insert into info values (3, 'Pradeep', 'kumar', 'Male', 'Patna', 'pradeep@gmail.com', 20000)
Insert into info values (1, 'kishor', 'kumar', 'Male', 'Noida', 'kishor@gmail.com', 60000)
Insert into info values (3, 'Pradeep', 'kumar', 'Male', 'Patna', 'pradeep@gmail.com', 20000)
Insert into info values (1, 'kishor', 'kumar', 'Male', 'Noida', 'kishor@gmail.com', 60000)
Insert into info values (2, 'vikash', 'kumar', 'Male', 'Patna', 'vikash@gmail.com', 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.

 

 

 

 

 

That’s It

READ NOW:- Top 10 SSL Certificate Provider

Leave a Reply

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