I have a MySQL table named Contacts_table with, say, the following contents:
ID NUMBER NAME CITY 001 2222222222 John Doe Los Angeles 002 3333333333 Rich Malfoy Houston 003 4444444444 Harry Potter New York 004 5555555555 Billy Bones Boston 005 6666666666 Joe Sanders Chicago
*ID: Primary key; auto-increment
*NUMBER: Unique key
And I am using PHP to read a comma-separated text dump of values for these fields, parse those values, and add them to the table. Say, a sample text dump has the following contents (shown here as table for clarity):
NUMBER NAME CITY 1234567892 Earl Grey Salem 3333333333 Rich Malfoy Sacramento 7656453248 Bill Gates New York 5555555555 James McGill Boston 6666666666 NULL Baton Rouge
Now, as you can see, there are some entries (based on NUMBER) in my TXT that do not exist in my table, i.e. 2nd, 4th, and 5th records. Then there are others that already have a corresponding entry in the table but with possibly different values on other fields. What I want my code to do is update new entries (with NUMBER values 3333333333, 5555555555, and 6666666666 in this example). As for other entries, the code would only update those fields where the TXT dump has a non-NULL value. Thus, this is what the updated table would look like:
ID NUMBER NAME CITY 001 2222222222 John Doe Los Angeles 002 3333333333 Rich Malfoy Sacramento 003 4444444444 Harry Potter New York 004 5555555555 James McGill Boston 005 6666666666 Joe Sanders Baton Rouge 006 1234567892 Earl Grey Salem 007 7656453248 Bill Gates New York
Like I said, wherever there's an existing entry, the fields have to be checked for values and only those fields are updated for that record which have a non-NULL values in the corresponding TXT dump.
I have a whole bunch of INSERT ON DUPLICATE KEY UPDATE queries (basically, one for each entry in the TXT dump) which is going to be a lot if the dump consists of hundreds of records. Is there any way to build a single query to handle this task in a single transaction?