Remove duplicate rows from table

Question

Possible Duplicate:
Remove duplicate rows in MySQL

I'd like to create an unique index on single attribute in a huge table, but there are duplicite values, so I need to remove the duplicity. Only duplicite records can be deleted. What is the best solution in phpmyadmin or mysql query to do this?

The only solution I figured out is to make select into another table, but I'd prefer to delete data from original table.

The table looks like this (I'd like to put unique index on id)

|id|...
+--+---
| 1|x
| 2|a   <-- duplicity
| 2|b   <-- duplicity
| 3|c
| 4|d

This would be the best result

|id|...
+--+---
| 1|x
| 2|a
| 3|c
| 4|d

This is also acceptable result

|id|...
+--+---
| 1|x
| 3|c
| 4|d

If I remember correctly, just creating a `UNIQUE` index will remove the duplicates. If not, just create a temporary table that will `INSERT ... SELECT DISTINCT id...` — Kermit, Oct 31 '12 at 14:31
@eggyal: `2, a` is not preferable than `2, b`, but one of them must go, I don't care which one. @njk: attempt to create unique index results in error — Jan Turoň, Oct 31 '12 at 14:33

score 4 · Accepted Answer · answered Oct 31 '12 at 14:34

4

ALTER IGNORE TABLE my_table ADD UNIQUE INDEX (id);

answered Oct 31 '12 at 14:34

eggyal

122,705
18
212
237

Works great, 10.000 rows removed instantly, thanks! – Jan Turoň Oct 31 '12 at 14:37
After having searched for that answer, I can confirm the first item (smallest ID) will be kept : http://stackoverflow.com/questions/5456520/mysql-alter-ignore-table-add-unique-what-will-be-truncated – gordie May 21 '14 at 11:56

score 1 · Answer 2 · answered Oct 31 '12 at 14:31

DELETE  a 
FROM    tableName a
        LEFT JOIN
            (
                SELECT id, min(colName) x
                FROM tableName
                GROUP BY id
            ) c
        ON a.id = c.id AND
            a.colName = c.x
WHERE c.id IS NULL;

SEE SQLFIDDLE Demo

score 1 · Answer 3 · edited Oct 31 '12 at 15:14

1

DELETE FROM table 
WHERE id IN 
   (SELECT id FROM table, (SELECT id, count(*) dNum FROM table GROUP BY id) x 
     WHERE id = x.id      
   ORDER BY id DESC LIMIT dNum-1)

edited Oct 31 '12 at 15:14

Jack

10,943
13
50
65

answered Oct 31 '12 at 14:56

MG_Bautista

2,593
2
18
33

Remove duplicate rows from table

3 Answers3