4

I have a table containing some duplicate values for 1 column, ie with table emails

id email
1  test@test.com
2  test@test.com
3  more@most.many 
4  cook@sheep.com

I'd like to remove row with id '2'. And I'd like to do this by creating a unique index of email thus forcing the table to drop redundancies.

I have seen this method referenced here(http://www.it-iss.com/mysql/sql-removing-duplicate-records/) and https://stackoverflow.com/questions/19000050/how-to-delete-first-of-a-double-record-with-alter-ignore-command-in-mysql

But when I attempt the statement alter ignore table emails_test add unique index(email)

I get a duplicate entry error for test@test.com, as if I never included the ignore keyword

Is there something I'm missing here? If this is not possible, what are alternative methods of deleting duplicates that are simpler than, say, using temporary tables MySQL Error 1093 - Can't specify target table for update in FROM clause

Community
  • 1
  • 1
rocketas
  • 1,679
  • 3
  • 18
  • 30
  • Interesting. I see the same behavior you're describing when I'm using mysql 5.5.13, but it works as it should under mysql 5.5.29. Maybe that's a bug that got fixed. If it's possible, try upgrading your server. – Trade-Ideas Philip Jan 03 '14 at 00:49
  • 3
    Turns out, this is due to an InnoDB bug. Try "set session old_alter_table=1;" See: http://stackoverflow.com/a/8053812/91165 – lilbyrdie May 05 '14 at 22:54

1 Answers1

14

You could try doing this as a few separate steps:

CREATE TABLE _emails LIKE emails
ALTER TABLE _emails ADD UNIQUE INDEX(email)
INSERT IGNORE INTO _emails SELECT * FROM emails
RENAME TABLE emails TO emails_old, _emails TO emails
tadman
  • 208,517
  • 23
  • 234
  • 262