Delete ALL duplicate rows in MySQL table (Not except 1, all duplicate rows)

Question

Possible Duplicate:
Remove duplicate rows in MySQL

I have a table "recipientscore" that looks like this:

recipientid / messageid
1 / 1
2 / 2
3 / 2
4 / 2
5 / 3
6 / 4

What I want to do is delete all the records that appear twice or more. I do not want to keep one version of each duplicate records. It would look like this after clean up:

recipientid / messageid
1 / 1
5 / 3
6 / 4

Would you have an idea on how to do that?

Thanks!

I would take distinct records into a temp table, drop the old and rename the temp to old name ;-) — bonCodigo, Dec 01 '12 at 17:56
I think this is _not_ a duplicate to the above question, since it's desired to drop **all** duplicates (i.e. _not_ drop all but one, and keep one). — Damon, Dec 01 '12 at 22:27

juergen d · Answer 1 · 2012-12-01T18:24:04.837

2

delete from recipientscore
where messageid in
(
   select * from
   (
      select messageid from recipientscore
      group by messageid
      having count(*) > 1
   ) x
)

SQLFiddle demo

edited Dec 01 '12 at 18:24

answered Dec 01 '12 at 17:55

juergen d

201,996
37
293
362

Hi Juergen d, I tried your method, but I have the following error: #1093 - You can't specify target table 'recipientscore' for update in FROM clause. What would you advise me to do? – user1029296 Dec 01 '12 at 18:03
Try my update. You can trick MySQL to do this with a subselect. – juergen d Dec 01 '12 at 18:13
When I do the select, it seems to me that it just works with rows that appear twice, not 3,4,5 times... – user1029296 Dec 01 '12 at 18:18
It works. See [this SQLFiddle demo](http://sqlfiddle.com/#!2/f8e8e/1) – juergen d Dec 01 '12 at 18:23

score 0 · Answer 2 · edited Dec 01 '12 at 18:41

0

The answer given by 'juergen d' is perfect. You could try this if u wish to keep just one unique row and delete the remaining duplicate rows.

ALTER IGNORE TABLE recipient score  ADD UNIQUE KEY idx1(messageid);

edited Dec 01 '12 at 18:41

juergen d

201,996
37
293
362

answered Dec 01 '12 at 17:57

Kanishk Dudeja

1,201
3
17
33

But that will keep one of each group, doesn't it? – Dec 01 '12 at 18:42

Delete ALL duplicate rows in MySQL table (Not except 1, all duplicate rows)

2 Answers2

SQLFiddle demo