MySQL Delete duplicates with different keys

Question

I messed up a couple of tables in my database with similar results but different keys. The results are something like this:

1   |2   |3   
5   |25  |25
6   |25  |25
7   |31  |31
8   |31  |31

I would like to delete those duplicate rows so that the results are like this:

1   |2   |3
5   |25  |25
7   |31  |31

I tried SELECT * DISTINCT 2 FROM TABLE but that didn't work. Sorry I can't word it much better!

juergen d · Accepted Answer · 2012-07-29T17:49:43.780

0

try

delete from your_table
where id not in (
                 select * from (select min(id) from your_table
                                group by col2, col3)
                 t_alias
                )

SQLFiddle example

edited Jul 29 '12 at 17:49

answered Jul 29 '12 at 17:37

juergen d

201,996
37
293
362

does this not assume that column2 and 3 are always identical? – Jon Taylor Jul 29 '12 at 17:41
Note: "NOT IN (...)" will do nasty things if col2 happens to be NULL. – wildplasser Jul 29 '12 at 17:41
@jon: You are right. I added `col2` to the group – juergen d Jul 29 '12 at 17:42
it didn't work, it just deleted the whole table except for one row (thank gods I was on a dummy table). thanks, anyway. – user1524011 Jul 29 '12 at 17:44
@user1524011: See [this SQLFiddle example](http://sqlfiddle.com/#!2/06616/1). It works there. – juergen d Jul 29 '12 at 17:49

score 0 · Answer 2 · answered Jul 29 '12 at 17:44

0

DELETE FROM the_table dt
WHERE EXISTS ( SELECT *
    FROM the_table ex
    WHERE ex.col2 = dt.col2
    AND ex.col3 = dt.col3
    AND ex.id < dt.id
    );

answered Jul 29 '12 at 17:44

wildplasser

43,142
8
66
109

MySQL Delete duplicates with different keys

2 Answers2