Help - Deleting LOTS of Duplicate Entires except keep 1 of them

camarosource · 04-04-2007, 03:21 AM

I have a database that stores all my RPO codes and it's descriptions. Everything was fine until I discovered (assuming) a search engine BOT must have run the script I wrote that imports the entire list into the database. I ran it once to import the list of 23,000+ RPO codes. But now it seems it was run 28 times more. So rather than 23,000+ in my MYSQL Database, I now have almost 652,000!! MOST of each duplicated 28 times or so!! Manually deleting 629,000+ Entries is NOT what I call fun, nor efficiant so I assume you can easily do this using a SQL QUERY..

I want to do the following:

1. Search for 2 fields in my database "RPO_CODES" and "DESCRIPTION"
2. DELETE ALL of the duplicate fields where "RPO_CODES" AND "DESCRIPTION" duplicate other entries
3. However keep 1 FIELD of each.. I only want to delete the DUPLICATE ENTRIES..

Example:

Entries.

1.

RPO Description
1. L98 5.7Liter V8 Engine for Camaro's and Corvettes
2. L98 5.7Liter V8 Engine for Camaro's and Corvettes
3. L98 5.7Liter V8 Engine for Camaro's and Corvettes
4. L98 5.7Liter V8 Engine for Camaro's and Corvettes
5. L98 5.7Liter V8 Engine for Camaro's and Corvettes
6. L98 5.7Liter V8 Engine for Camaro's and Corvettes
7. L98 5.7Liter V8 Engine for Camaro's and Corvettes
8. L98 5.7Liter V8 Engine for Camaro's and Corvettes
9. L98 5.7Liter V8 Engine for Camaro's and Corvettes
10. L98 5.7Liter V8 Engine for Camaro's and Corvettes

So it ends up with just 1 entry

1. L98 5.7Liter V8 Engine for Camaro's and Corvettes

Thanks very much!

guelphdad · 04-04-2007, 10:40 AM

Code:

ALTER IGNORE TABLE cars
ADD PRIMARY KEY deletelater(rpo_codes, description);

you can now leave that index or you can drop it

Code:

ALTER TABLE cars
DROP index deletelater