rocketas rocketas - 2 months ago 7
MySQL Question

"Alter ignore" + "unique key" to remove duplicates, mysql and sql server

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 http://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

Answer

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
Comments