Mysql – Update multiple rows with different columns


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?

Best Answer

Something like:

INSERT INTO user (id, name, address, telephone)
    (1, 'Tim', NULL, NULL),
    (2, NULL, 'America', '000')
    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.