SQL Entry's Duped

Question

I messed up when trying to create a test Database and accidently duplicated everything inside of a certain table. Basically there is now 2 of every entry there was once before. Is there a simple way to fix this? (Using InnoDB tables)

Answer 1

Yet another good reason to use auto incrementing primary keys. That way, the rows wouldn't be total duplicates.

Probably the fastest way is to copy the data into another table, truncate the first table, and re-insert it:

create temporary table tmp as
   select distinct *
   from test;

truncate table test;

insert into test
    select *
    from tmp;

As a little note: in almost all cases, I recommend using the complete column list on an insert statement. This is the one case where it is optional. After all, you are putting all the columns in another table and just putting them back a statement later.