Topic: MYSQL Delete duplicates?

[Minion]

I need to remove any rows in a table that that have exact same info.

So if there is 3 exact duplicates, it will delete 2 and leave one.

I messed up and didn't set up a check for exsisting entries before INSERT

Is there a way to do this through just a query?

[Spaceman-Spiff]

no, u have to run a query to check if there are any duplicates, and delete the duplicates

example removing duplicate emails (pseudo code):
query(SELECT ID, COUNT(*) AS total FROM members GROUP BY email)
while blablabla {
if $row[total] > 1
    query(DELETE FROM members WHERE ID=$row[ID] LIMIT $row[total]-1)
}

[Iridium]

A possibly quicker method would be to do the following:

• Create a new table identical in structure to the first, but with no data.
• Use the query: INSERT INTO `name_of_new_table` SELECT DISTINCT * FROM `name_of_old_table`
• Drop the old table
• Rename the new table to whatever the old table was called.

This has the advantage of being pretty fast, and only requiring 4 queries, no matter how many rows are duplicated. On the other hand though, if there are any database queries on the old table between the drop and the rename, they will fail (since the table won't exist during that period). That being said, the period for which this is a problem should be pretty short.

[Minion]

Very good.

I will be trying both out.
Thanks alot!

[[Unknown]]

I like Iridium's method and will be remembering it for the future...

-[Unknown]

[Minion]

Thanks Iridium
                                                         ,
That worked like a charm.