Help - Deleting LOTS of Duplicate Entires except keep 1 entr

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
camarosource
Forum Commoner
Posts: 77
Joined: Sat Aug 03, 2002 10:43 pm

Help - Deleting LOTS of Duplicate Entires except keep 1 entr

Post by camarosource »

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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Rename the table. Create a duplicate (in structure) table then use SELECT DISTINCT .. INTO
Post Reply