Remove duplicate MySQL record?

Question

I've got a table where one month's worth of data got duplicated. So there are about 7 fields that are identical. For the time being I've moved that month (2x the records) into a separate table. How do I get rid of the duplicates?

From my limited MySQL knowledge, I can't see a way to do this other than manually (but it's not practical since there are 125k records).

thanks!

Update: Not all fields are perfectly identical so the distinct insert into a new table didn't work. One thing I do have is that location_id captures the duplicates. So location when ordered looks like: 1,2,3,3,4,4,5,5,6,6,7,8,8....

So what I really want is order by location_id then grab the first record for that location and forget the rest.

Answer 1

Create a new table an just select them with DISTINCT into the new table

Not tested but should look like this:

INSERT INTO new_table(field1, field2, field3) SELECT DISTINCT field1, field2, field3 FROM old_table

See the INSERT...SELECT page in the docs