Delete Duplicate Rows In SQL

1. To delete duplicate records from a table having no identity column in it.

lets take an example to implement this:

create table Employee
(
EmpDeptID int,
EMpName varchar(100),
salary numeric
)

insert into Employee values(2,'Peter',5000)
insert into Employee values(3,'Peter',100)
insert into Employee values(24,'Mary',5000)
insert into Employee values(6,'Anthony',500)
insert into Employee values(5,'Jacob',1000)
insert into Employee values(3,'Peter',5000)
insert into Employee values(0,'Jacob',1500)
insert into Employee values(2,'Peter',4500)

select * from employee

Now we will delete the duplicate rows using this below query :

;with getdata as
(
select *,ROW_NUMBER() OVER(partition by EMpName ORDER BY EmpDeptID ) as UniqueCol from employee 
)
delete from getdata where UniqueCol > 1


I deleting duplicate rows Using CTE. i.e Common Table Expression.

Another way of deleting the duplicate records without using CTE :

delete test from 
(select *,ROW_NUMBER() OVER(partition by EMpName ORDER BY EmpDeptID ) as UniqueCol from employee) test 
where UniqueCol > 1


2. Now lets take an example for a table having an identity column in it.

Here, we can use the above two queries. But there is also an another way of deleting records for a table having identity column.

create table EmployeeIdentity
(
EmpDeptID int identity,
EMpName varchar(100),
salary numeric
)


insert into EmployeeIdentity(EMpName,salary) values('Peter',5000)
insert into EmployeeIdentity(EMpName,salary) values('Peter',100)
insert into EmployeeIdentity(EMpName,salary) values('Mary',5000)
insert into EmployeeIdentity(EMpName,salary) values('Anthony',500)
insert into EmployeeIdentity(EMpName,salary) values('Jacob',1000)
insert into EmployeeIdentity(EMpName,salary) values('Peter',5000)
insert into EmployeeIdentity(EMpName,salary) values('Jacob',1500)
insert into EmployeeIdentity(EMpName,salary) values('Peter',4500)


select * from EmployeeIdentity

Query to delete duplicate records:

--For Tables with Identity Coloumns:

delete from EmployeeIdentity where EmpDeptID not in
 (select min(EmpDeptID) from EmployeeIdentity group by Empname)