All Type Coding

Search Here

How to delete duplicate records from a table in sql server.?

Here I am explaining How can we delete all duplicate rows except one from a sql server table
Here We will be using Employee2 table for this demo


Now How to achieve the above  table 

















For Creating Employee2 table execute below script

Create table Employees

(
     EmpID int,
     EmpFirstName nvarchar(50),
     EmpLastName nvarchar(50),
     EmpGender nvarchar(50),
     EmpSalary int
)


Now insert the data into Employee2 table

Insert into Employee2 values (1, 'Ram', 'Singh', 'Male', 60000),
(1, 'Ram', 'Singh', 'Male', 60000),
(1, 'Ram', 'Singh', 'Male', 60000),
(2, 'Rani', 'Raj', 'Female', 80000),
(2, 'Rani', 'Raj', 'Female', 80000),
(2, 'Rani', 'Raj', 'Female', 80000),
(3, 'Dev', 'Sagar', 'Male', 40000),
(3, 'Dev', 'Sagar', 'Male', 40000),
(3, 'Dev', 'Sagar', 'Male', 40000),
(4, 'Narendra', 'Singh', 'Male', 30000),
(4, 'Narendra', 'Singh', 'Male', 30000),
(4, 'Narendra', 'Singh', 'Male', 30000)

The delete query should delete all duplicate rows except one. The output should be as shown below, after the delete query is executed. 








Here we can delete data using  PARTITION BY divides the query result set into partitions.

WITH Emp2 AS
(
   SELECT *, ROW_NUMBER()OVER
   (PARTITION BY EmpID ORDER BY EmpID) AS RowNumber
   FROM Employee2
)
DELETE FROM Emp2 WHERE RowNumber > 1














Now execute the select statement and see no duplcate data in datatable















Thus we can delete duplicate data from database table



No comments :

Post a Comment