A simple way out to delete duplicate entries is to copy DISTINCT values to a temporary table. I tried it over 2 field table with 0.1 million rows and it worked perfectly for me.
- CREATE TABLE NewTable ...;
- INSERT INTO NewTable(field1)
- SELECT DISTINCT field1 FROM MyTable;
- DROP TABLE MyTable;
CREATE TABLE NewTable ...; INSERT INTO NewTable(field1) SELECT DISTINCT field1 FROM MyTable; DROP TABLE MyTable;
Alternately, if you’re populating your database and would like to avoid duplicate entries, modify your INSERT queries to *REPLACE. For e.g.
- REPLACE INTO table1 (field1, .. , ..) VALUES ('value1', .. , ..)
REPLACE INTO table1 (field1, .. , ..) VALUES ('value1', .. , ..)In case you want to delete the duplicate records from the given table, you can try executing:
- Delete bad_rows.*
- FROM MyTable AS bad_rows
- INNER JOIN (
- SELECT field1, MIN(id) AS min_id
- FROM MyTable
- GROUP BY field1
- HAVING COUNT(*) > 1
- ) AS good_rows ON good_rows.field1 = bad_rows.field1
- AND good_rows.min_id <> bad_rows.id;
discuss this topic to forum
