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

Question

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

Answer 1

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