Mysql – Remove duplicate records from table, leaving one copy

duplicationMySQL

I have inherited a MySQL table from one of my colleagues, and am in need of a little advice.

The table has the following structure:

| PHONE_NO   | ACC_NO | GET_MAIL | DATETIME_CRE         | INT_ACC | MAIL |
| 0113313313 | 000001 |   YES    | 2003-01-01 19:00:00  | 9000001 | N/A  |

There is no primary key, as the table was not created with one. I cannot recreate the table as:

  1. It would take far too long to complete (over 2 million records and counting)
  2. This is a mission critical table, meaning taking it down would result in my client losing money for the duration.

I need to be able to remove duplicated rows from the table with a query, without inserting a primary key. "Duplicated" in this sense refers to all columns within the row being identical to another row.

EDIT
This is the result of show create table query, as requested by ypercube;

CREATE TABLE `callerQue` (
  `PHONE_NO` varchar(30) DEFAULT NULL,
  `ACC_NUMBER` varchar(40) DEFAULT NULL,
  `GET_MAIL` varchar(6) DEFAULT NULL,
  `DATETIME_CRE` datetime DEFAULT NULL,
  `INT_ACC` varchar(40) DEFAULT NULL,
  `MAIL` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

Best Answer

Not sure if this would work in mysql since i am a MSSQL-dba but you could add an identity column -> auto_increment. This would make all rows unique. Then you could do a group by on duplicate records and select the highest ID for example. This way you will only delete one row. Repeat this until no more duplicates.

Related Question