Removing duplicates from mysql table?

Question

I have a fairly simple test database:

id email 1 [email protected] 2 [email protected] 3 [email protected] 4 [email protected] 5 [email protected] 6 [email protected] 7 [email protected] 8 [email protected] 9 [email protected]

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

id email 1 [email protected] 2 [email protected] 3 [email protected] 5 [email protected] 7 [email protected] 8 [email protected]

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: stackoverflow.com/questions/672702/… 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
– John Hall, Commented Jun 19, 2013 at 12:59

user925885 · Accepted Answer · 2013-06-19 12:46:09Z

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, 2013 at 12:46

user925885

Sign up to request clarification or add additional context in comments.

2 Comments

Bogdan Burym Over a year ago

This will give Duplicate entry .. for key ... as far as I know

Bogdan Burym Over a year ago

Found reason why stackoverflow.com/questions/8053447/…

Bogdan Burym · Accepted Answer · 2013-06-19 13:10:59Z

@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);

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 :(

Collectives™ on Stack Overflow

Removing duplicates from mysql table?

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related