Remove duplicate MySQL record?

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_开发者_C百科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.

---

First

Take backup

Second

Create a temp_table and insert all data into temp_table with grouping

CREATE TABLE temp_table (column1, column2, ...., column7) 
    SELECT column1, column2, ...., column7 FROM temp_table 
    GROUP BY column1 /* which ever column you want it the unique or distinct way */
        /* If you have any condition , you can a syntax here with WHERE clause */

Third

If you think, you got the desired result you can rename the temp_table to the new table and drop or rename the old table

Hope you, may get little idea or may not.

---

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

---

You can add an auto-increment primary key to the table (as suggested by another answer that has been deleted).

ALTER TABLE location ADD COLUMN id INT AUTO_INCREMENT, ADD PRIMARY KEY (id);

SELECT * FROM location;

+-------------+--------------+----+
| location_id | othercolumns | id |
+-------------+--------------+----+
|           1 | text         |  1 |
|           2 | text         |  2 |
|           3 | text         |  3 |
|           3 | text         |  4 |
|           4 | text         |  5 |
|           4 | text         |  6 |
|           5 | text         |  7 |
|           5 | text         |  8 |
|           6 | text         |  9 |
|           6 | text         | 10 |
|           7 | text         | 11 |
|           8 | text         | 12 |
|           8 | text         | 13 |
+-------------+--------------+----+

Then you can use MySQL's multi-table DELETE syntax to make sure you only delete rows with a matching location_id and a higher id.

DELETE l2
FROM location l1
JOIN location l2 ON l1.id < l2.id AND l1.location_id = l2.location_id;

SELECT * FROM location;

+-------------+--------------+----+
| location_id | othercolumns | id |
+-------------+--------------+----+
|           1 | text         |  1 |
|           2 | text         |  2 |
|           3 | text         |  3 |
|           4 | text         |  5 |
|           5 | text         |  7 |
|           6 | text         |  9 |
|           7 | text         | 11 |
|           8 | text         | 12 |
+-------------+--------------+----+