Simple Machines Community Forum

General Community => Scripting Help => Topic started by: Minion on August 08, 2003, 01:26:31 AM

Title: MYSQL Delete duplicates?
Post by: Minion on August 08, 2003, 01:26:31 AM
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?
Title: Re:MYSQL Delete duplicates?
Post by: Spaceman-Spiff on August 08, 2003, 02:06:59 AM
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)
}
Title: Re:MYSQL Delete duplicates?
Post by: Iridium on August 08, 2003, 07:08:34 PM
A possibly quicker method would be to do the following: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.
Title: Re:MYSQL Delete duplicates?
Post by: Minion on August 08, 2003, 11:54:12 PM
Very good.

I will be trying both out.
Thanks alot!
Title: Re:MYSQL Delete duplicates?
Post by: [Unknown] on August 09, 2003, 12:10:51 AM
I like Iridium's method and will be remembering it for the future...

-[Unknown]
Title: Re:MYSQL Delete duplicates?
Post by: Minion on August 10, 2003, 12:57:30 AM
Thanks Iridium
                                                         ,
That worked like a charm.