Remove Duplicates in MySQL

Duplicates are nothing but, repeated data of database objects(table,view). Find the duplicates in MySQL.

Be careful about removing duplicates. Before deleting records(duplicates) of table, its better to run the query on copy of the table.

Following table named ’emp1′ contains duplicates in the column empno and empname

+-------+-----------+ | empno | empname | +-------+-----------+ | 1000 | Karunakar | | 1001 | Kalpesh | | 1002 | NULL | | 1003 | James | | 1003 | James | | 1004 | Pearson | | 1004 | Pearson | +-------+-----------+

Use below queries for removal of duplicate rows.

DELETE e1 FROM emp1 e1, emp1 e2 WHERE e1.empname < e2.empname AND e1.empno = e2.empno;

DELETE e1 FROM emp1 e1, emp1 e2 WHERE e1.empno < e2.empno AND e1.empname = e2.empname;

Best approach is:

1. create temporary table

create table temp_emp1(empno decimal(4,0),empname varchar(20));

2. insert unique data(non duplicate rows) into that.

INSERT INTO temp_emp1(empno,empname) SELECT DISTINCT empno,empname FROM emp1;

3. Then, drop the original table. To

drop table emp1;

4. create original table

create table emp1(empno decimal(4,0),empname varchar(20));

5. Insert temporary data into original table.

INSERT INTO emp1(empno,empname) SELECT DISTINCT empno,empname FROM temp_emp1;

Verify data in original table.

mysql> select * from emp1;

+-------+-----------+ | empno | empname | +-------+-----------+ | 1000 | Karunakar | | 1001 | Kalpesh | | 1002 | NULL | | 1003 | Steve | | 1003 | James | | 1004 | Pearson | +-------+-----------+

6. Create Unique index on original table emp1 to avoid duplicates in future.

We can delete duplicates using temporary table. Following query deletes all the duplicate records.

CREATE TEMPORARY TABLE IF NOT EXISTS _emp1 AS (SELECT empno FROM emp1 dub GROUP BY dub.empno, dub.empname HAVING COUNT(*) >1);

DELETE FROM emp1 WHERE empno IN (SELECT empno FROM _emp1);

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: