XMPP JavaScript Library

Deleting duplicate rows from database.

SQL, Sachin Puri, 2010-04-25 22:02:19

--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

 

Note: Please take backup of your table before using this query.
Add Your Comment
   
    Yes! I want to receive all comments by email

No Comments Posted Yet. Be the first one to post comment