0

I have a table with these fields and (sample) data:

id | dropperid | itemid
 1 | 933101    | 513     
 2 | 330400    | 381     
 3 | 868939    | 585    
 4 | 111111    | 646     
 5 | 933101    | 513     
 6 | 933101    | 513     
 7 | 943844    | 513

How do I remove all duplicates, where "duplicate" means the same dropperid and itemid?

In the sample above, I want to remove id 5 and 6.

Is there a way I can do this to my entire table? I don't want to write in every single dropperid and itemid to remove duplicates.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
John K
  • 11
  • 5

2 Answers2

0

Just add an UNIQUE index:

ALTER IGNORE TABLE MY_TABLE
ADD UNIQUE INDEX drop_rel_idx (id);

This will drop all the duplicate rows; in particular it would keep the oldest duplicate record erasing the newer ones.

See more:

Remove duplicate rows

Community
  • 1
  • 1
antoniodvr
  • 1,259
  • 1
  • 14
  • 15
  • drop_rel_idx? I'm not sure what you mean? What is that supposed to be? – John K Nov 22 '15 at 21:42
  • The index name for your table, you can use what you want. I supposed that table would contain relationship between dropper and item so I wrote `drop_rel_idx`. `id` is the name of the first column you want to keep. – antoniodvr Nov 22 '15 at 21:48
  • note that that will permanently change the structure of your table, which you might not want / be allowed to do. – simonalexander2005 Nov 23 '15 at 14:50
0

Use mysql's multi-table delete syntax over a join of the table to itself on the duplicate condition, while targeting the high ids:

delete t1
from mytable t1
join mytable t2 on t1.dropperid = t2.dropperid
and t1.itemid = t2.itemid
and t1.id > t2.id

The last condition is the key: It not only prevents matches of rows to themselves, but splits the rows into two buckets; t2 containing the lowest ids of dupes and t1 the rest, so this is will keep the lowest id of duplicates no matter how many duplicates there are (ie triples, quadruples etc).

Bohemian
  • 412,405
  • 93
  • 575
  • 722