SELECT and UPDATE in a single query while updating selective fields of duplicate records only

Question

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?

Answer 1

Since there is a unique constraint/index on number, you can use ON DUPLICATE KEY UPDATE syntax - which can be combined with INSERT ... VALUES so all the rows are inserted/updated in one statement. The additional requirement that new null values do not update existing ones can be satisfied using COALESCE() function. Example:

INSERT INTO Contacts
 (number, name, city)
VALUES
 (1234567892, 'Earl Grey', 'Salem'),
 (3333333333, 'Rich Malfoy', 'Sacramento'),
 (7656453248, 'Bill Gates', 'New York'),
 (5555555555, 'James McGill', 'Boston'),
 (6666666666, NULL, 'Baton Rouge')
ON DUPLICATE KEY UPDATE
 name = COALESCE(VALUES(name), name),
 city = COALESCE(VALUES(city), city) ;

Test at SQLfiddle

• TheLearner: This does exactly what I wished for. Thank you so much!!!

Answer 2

Another approach:

1. LOAD DATA into a new (temp) table
2. UPDATE real JOIN new ON ... SET real.x=... WHERE ... -- Update any existing rows that need updating
3. INSERT INTO new SELECT new LEFT JOIN real ON ... WHERE real.id IS NULL -- Add any rows that do not already exist