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
For Creating Employee2 table execute below script
Thus we can delete duplicate data from database table
Here We will be using Employee2 table for this demo
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