Deleting duplicate rows from database.

--Create table Employee
create table emp(
emp_id char(5) not null primary key,
emp_name varchar(100) not null,
dept_id char(5) not null)

--Entries in emp table
insert into emp values('A001','Jon','ACC');
insert into emp values('A002','Sachin Puri','IT');
insert into emp values('A003','Saurabh Kumar','IT');
insert into emp values('A004','Ankur Datta','IT');
insert into emp values('A005','Rajesh Kumar','ACC');
insert into emp values('A006','Amit Kumar','ACC');
insert into emp values('A007','Manoj Prabhakar','HR');

--Double Entries (Duplicate Entries)
insert into emp values('A008','Amit Kumar','ACC');
insert into emp values('A009','Rajesh Kumar','ACC');
insert into emp values('A010','Manoj Prabhakar','HR');
insert into emp values('A011','Sachin Puri','IT');

MSSQL Server

--See duplicate rows
select emp_name,count(*) no_of_entries
from emp
group by emp_name
having count(*)>1

--Delete duplicate rows
delete from emp
where emp_id not in(
select min(emp_id) as emp_id from emp group by emp_name)

--See the result
select * from emp

Delete duplicate rows from MySQL Server

--See duplicate rows

SELECT e1.*
FROM emp e1 join emp e2 on e1.emp_name=e2.emp_name
where e1.emp_id>e2.emp_id

--Delet duplicate rows from mysql table

DELETE e1.*
FROM emp e1 join emp e2 on e1.emp_name=e2.emp_name
where e1.emp_id>e2.emp_id