SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    May 2001
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complex SQL query.

    Hi,
    I have a database of about 20,000 members. It contains their address, city, state, country, company details. I need to find duplicates in this and delete them. Does anyone have any suggestions on how one can do this?
    unregistered1.

  2. #2
    SitePoint Evangelist worksdev's Avatar
    Join Date
    Mar 2002
    Location
    Central, PA - originally from Monterey, CA
    Posts
    497
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello:

    Do you have a single field that you want to have as your unique key (such as company name)?

    If so you can use:

    Code:
    ALTER IGNORE TABLE mytable ADD UNIQUE (CompanyName);
    Or whatever your unique key will be. This will delete duplicates based on the unique key, and prevent further duplicates.

    I found this tip at: http://www.geocrawler.com/archives/3.../8/50/6523727/

    Best Regards,
    worksdev
    Church Website Design by Enlighten Web Services -
    Web design-hosting-support for Christian ministries
    Friendly Community of Webmasters
    Christian Websites :: Christian Web Design

  3. #3
    SitePoint Addict
    Join Date
    May 2001
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,
    I cant use distinct or unique because there is no single column that identifies the record.

    I need to first sort by street address, then company name, then personal name. I need to do a layered sorting. Can this be done?
    unregistered1.

  4. #4
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know about MySQL, but with other db you can:
    Code:
    SELECT count(1), street, company, first_name, last_name
    FROM user
    GROUP BY street, company, first_name, last_name
    HAVING count(1) > 1
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  5. #5
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  6. #6
    SitePoint Evangelist worksdev's Avatar
    Join Date
    Mar 2002
    Location
    Central, PA - originally from Monterey, CA
    Posts
    497
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello unregistered1:

    Did sweatje's solution help you. I know the query works with mySQL.

    If you don't have one already, you can add an auto increment unique id field to your table. Then in the select query add 'id' .

    When you see your query results, you will see the id number for one of each duplicate records. You can then run a delete query to delete these record ids.

    There are probably easier ways to do this, but none come to mind.

    Best Regards,
    worksdev
    Church Website Design by Enlighten Web Services -
    Web design-hosting-support for Christian ministries
    Friendly Community of Webmasters
    Christian Websites :: Christian Web Design

  7. #7
    SitePoint Addict
    Join Date
    May 2001
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,
    I just realised I could simply sort by the ZIP code and then once I find matching ZIP codes, match street addresses. This would save a lot of extra sorting.

    I have been working on finding an efficient solution for a little over 3 days now and finally, the solution flashes before my eyes while starring at the blank screen.

    thanks for the help everyone.

    unregistered1.


Bookmarks

Posting Permissions

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