SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    645
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Joining to tables?

    I have one table called members with a lot of names and e-mails and other things.

    Now I have imported a csv file into a new table called new_members

    But I know that the new table have some duplicate e-mails and sometimes theres is only an e-mail, but no name of the contact.
    So, what I need to do is to move all members from new_members into the table members.
    But if the e-mail is already in the old table, then I should ignore that one. And if there are two identical e-mails in the new table and one is missing the contact name, then I want to store the one that has the contact name.

    Maybe I have to make a temporary table first. So I can move the new_members and do the check for duplicates before joining the two tables?

    My idea is to check new_members if there are duplicates of the field email and if it finds two (or more), then it should use the one that have something in the contact field. Then store those in temporary_members.
    But I don't know how to write the best MySql Query for that.

    Then I want to move all the members from temporary_members to the members table. But this time ONLY check if the email is already there. I don't want to replace anything in the old table.

    So, any idea on how to do this the best way? I know there's a lot of clever people out here with great ideas and a lot of do's and don't do's.

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    In MySQL there's keywords INSERT IGNORE and INSERT ON DUPLICATE KEY. This will allow you to determine what happens on a primary key clash.

    My suggestion here though is this: There is no shame in using something like Excel to stage your data into a normalize data set first. It is often faster and can save you even more time when you avoid some key stroke or operation that messes up your data in the table.


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
  •