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.
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.