Removing duplicates from mysql table?

Question

I have a fairly simple test database:

id  email
1   blue@yellow.com
2   pink@test.com
3   ace@ace.com
4   ace@ace.com
5   peter@something.com
6   peter@something.com
7   mouse@mouse.com
8   monkeyman@test.com
9   mouse@mouse.com

I want to remove duplicates which should alter the table to:

id  email
1   blue@yellow.com
2   pink@test.com
3   ace@ace.com
5   peter@something.com
7   mouse@mouse.com
8   monkeyman@test.com

Or with a new id it does not matter.

I stumbled upon this titbit:

mysqli_query($con,"ALTER IGNORE TABLE mytest ADD UNIQUE (email,id)");

But, alas, it does nothing. What am I doing wrong?

Potential Duplicate: http://stackoverflow.com/questions/672702/how-to-delete-duplicates-in-mysql-table If ID is your primary key, it is already unique. You can simply: `ALTER IGNORE TABLE my_table ADD UNIQUE(email)` Tested and confirmed. — John Hall, Jun 19 '13 at 12:59

score 4 · Answer 1 · answered Jun 19 '13 at 12:46

4

Try mysqli_query($con,"ALTER IGNORE TABLE mytest ADD UNIQUE (email)");.

UNIQUE (email,id) means the id and email pair should be unique, which it never is since I assume id is a primary key.

answered Jun 19 '13 at 12:46

This will give `Duplicate entry .. for key ...` as far as I know – Bogdan Burym Jun 19 '13 at 13:06
Found reason why http://stackoverflow.com/questions/8053447/mysql-alter-ignore-table-gives-integrity-constraint-violation – Bogdan Burym Jun 19 '13 at 13:09

score 1 · Answer 2 · answered Jun 19 '13 at 13:10

1

@Havsmonstret solution is good, as your is.
It should work as described in documentation http://dev.mysql.com/doc/refman/5.1/en/alter-table.html.
But something is wrong. It does not.
Since there is bug in some recent versions of InnoDB http://bugs.mysql.com/bug.php?id=40344

Try this queries, it worked for me:

SET SESSION old_alter_table=1;
ALTER IGNORE TABLE mytest ADD UNIQUE (email,id);

answered Jun 19 '13 at 13:10

Bogdan Burym

5,482
2
27
46

small shoutout thanks from the future, apparently it's been like 10 years and mysql still hasn't fixed it. Though I guess that's about to be expected, it's also sadly present in MariaDB :( – Rogue Jan 08 '17 at 01:17

Removing duplicates from mysql table?

2 Answers2