Update multiple rows with different columns

Question

I am trying to perform a bulk MySQL update where I know only certain columns need to be updated for each row, e.g. row A only "name" changed to "Sue", row B "name" and "address" changed to "Joe" and "Evergreen Terrace", etc. All the columns and data may be different.

Most multi-row examples typically update the same columns for each row, but is there a way for the SQL command to specify only what's changed per row?

Answer 1

Something like:

INSERT INTO user (id, name, address, telephone)
VALUES
 (1, 'Tim', NULL, NULL),
 (2, NULL, 'America', '000')
ON DUPLICATE KEY UPDATE
 name = IF(ISNULL(VALUES(name)), name, VALUES(name)),
 address = IF(ISNULL(VALUES(address)), address, VALUES(address)),
 telephone = IF(ISNULL(VALUES(telephone)), telephone, VALUES(telephone))

will work (leaving the NULL fields untouched), presuming the id field is the primary key and will trigger the DUPLICATE KEY UPDATE, although I can't comment on how efficient it would be.

Answer 2

If the new data is in a separate table, you can do a "multi-table" update to transfer the data into the main table.

To handle the "optional" address, it could be NULL in the separate table, then use IF() or IFNULL() or some other conditional code to leave the old value alone.

But I would probably simply create a bunch of separate UPDATE statements.

• mahemoff: The source data is from an external source unfortunately.
• Rick James: Then I would build a temp table with that data, then do 2 updates -- one to transfer all the 2-column data; one to transfer all the 1-column data.