Delete Duplicates Leaving One of Two Identical Records

One of my MySQL database tables contains four BLObs in each row. When it was first set up the table had no keys. However when I now try to add a Primary Key (PK) I find I cannot do so as one of my people ran the same add twice. As a result I now have nearly a thousand duplicate records in over 5000 total records. Having Googled and searched here for a bit there seems more than one way of removing duplicates but which is the best one to use here?

Thank you...

  • 1
    Posting the SHOW CREATE TABLE output would help. – ypercubeᵀᴹ Mar 5 '15 at 11:09

Without knowing the structure, generically, you can GROUP BY the fields that have been duplicated and store it in a separate table for check purposes, then substitute the old table with the new one.

So, imagine that I have a table my_table with the fields a and b duplicated. I can do:

CREATE TABLE my_table_new (id int primary key auto_increment, a blob, b blob);

SET @i := 0;

INSERT INTO my_table_new SELECT @i := @i + 1, a, b FROM my_table GROUP BY a, b;

The exact query will depend on the older structure, the fields you want to group by, the order in which you want to insert, etc. This example creates an integer primary key from 1 to number of non-duplicate records.

  • 1
    Why the @i := @i + 1? It's an auto-increment column, isn't it? – ypercubeᵀᴹ Mar 5 '15 at 11:10
  • True, not needed. I think I put it because I was going to tell him/her to check with the select first if that is what it was required, then insert it. Or maybe use it to insert it to the same table, after an offset. What can I say, I love LEFT JOINS and counters on my SELECTS :-)! – jynus Mar 5 '15 at 11:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.