I have a table containing some duplicate values for 1 column, ie with table emails
id email
1 [email protected]
2 [email protected]
3 [email protected]
4 [email protected]
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 [email protected], 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
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