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

MySQLoptimizationPHPquery-performanceselectupdate

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?

Best Answer

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

Related Question