Delete Duplicate Records in SQL

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

For better understanding, lets 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, lets 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 only unique record in the table. After delete, the output look.

 

Comments are closed