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

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
– bonCodigo, Commented Dec 1, 2012 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
– Damon, Commented Dec 1, 2012 at 22:27

juergen d · Accepted Answer · 2012-12-01 18:24:04Z

2

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

SQLFiddle demo

edited Dec 1, 2012 at 18:24

answered Dec 1, 2012 at 17:55

juergen d

205k40 gold badges303 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1029296 Over a year ago

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?

juergen d Over a year ago

Try my update. You can trick MySQL to do this with a subselect.

user1029296 Over a year ago

When I do the select, it seems to me that it just works with rows that appear twice, not 3,4,5 times...

juergen d Over a year ago

It works. See this SQLFiddle demo

juergen d · Accepted Answer · 2012-12-01 18:41:42Z

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 1, 2012 at 18:41

juergen d

205k40 gold badges303 silver badges376 bronze badges

answered Dec 1, 2012 at 17:57

Kanishk Dudeja

1,2213 gold badges17 silver badges34 bronze badges

1 Comment

user330315 Over a year ago

But that will keep one of each group, doesn't it?

Collectives™ on Stack Overflow

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

2 Answers 2

SQLFiddle demo

4 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Linked

Related