Advertisement:

Author Topic: MYSQL Delete duplicates?  (Read 43745 times)

Offline Minion

  • Semi-Newbie
  • *
  • Posts: 37
  • Gender: Male
  • Screen Paper Skin
    • Some of my photos
MYSQL Delete duplicates?
« 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?
« Last Edit: August 08, 2003, 01:56:36 AM by Minion »

Offline Spaceman-Spiff

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 1,524
  • Gender: Male
Re:MYSQL Delete duplicates?
« Reply #1 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)
}

Offline Iridium

  • Semi-Newbie
  • *
  • Posts: 20
  • Gender: Male
    • XiSoft
Re:MYSQL Delete duplicates?
« Reply #2 on: August 08, 2003, 07:08:34 PM »
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.

Offline Minion

  • Semi-Newbie
  • *
  • Posts: 37
  • Gender: Male
  • Screen Paper Skin
    • Some of my photos
Re:MYSQL Delete duplicates?
« Reply #3 on: August 08, 2003, 11:54:12 PM »
Very good.

I will be trying both out.
Thanks alot!

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
Re:MYSQL Delete duplicates?
« Reply #4 on: August 09, 2003, 12:10:51 AM »
I like Iridium's method and will be remembering it for the future...

-[Unknown]

Offline Minion

  • Semi-Newbie
  • *
  • Posts: 37
  • Gender: Male
  • Screen Paper Skin
    • Some of my photos
Re:MYSQL Delete duplicates?
« Reply #5 on: August 10, 2003, 12:57:30 AM »
Thanks Iridium
                                                         ,
That worked like a charm.