MySQL: Remove Duplicate Rows/Records from Table

How to remove duplicate entries from a table? Here are few methods for deleting duplicate rows/records from a table in mysql. The same concept will work in other database table for example, Oracle, as well.

Methods of deleting duplicate records in a table:-
1. Alter table for adding unique index to table
If I can add unique index to a column or set of columns which I think must be unique then I can remove those duplicate records. I will try to add unique index and ignore those duplicates records that will be left from inclusion into table.

Table::rank
IDLANGUAGERANKING
1PHP4
2C#8
3PHP4
Here duplicates are 1st and 3rd row.
Query:

Alter IGNORE table rank add unique key (LANGUAGE, RANKING);
Here table only accepts unique value for LANGUAGE and RANKING. By this query, duplicates values will be dropped (deleted) and the result table will only have unique records.
2. Using Temporary table
I will create a temporary table and transfer all the distinct rows to the temporary table. I will delete all records from original table, including duplicates, and transfer the temporary table's data to original table.

Mysql Query for the method of deleting duplicates:


a. Create temporary table tmp_tbl as select Distinct * from org_table;

As, I have used distinct keyword, no duplicate rows will come into new table. Same result will be achieved by using these two queries one by one:


A.1> Create temporary table tmp_tbl (ID int, LANGUAGE varchar (20), RANKING smallint(2)) type = Heap;
A.2> Insert into tmp_tbl (ID, LANGUAGE, RANKING) select distinct ID, LANGUAGE, RANKING from org_table;

And, now
B> truncate table org_table, or Delete from org_table;
C> Insert into org_table (ID, LANGUAGE, RANKING) select ID, LANGUAGE, RANKING from tmp_tbl;

Now, my original table does not have any duplicate records.

  • # 1 - by handa

    thanks this works

  • # 2 - by handa

    thanks this works

Comments are open for an year period. Please, write here on Facebook page.