Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Aug 2002
    Location
    Vancouver, BC
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •