4

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?

0
4

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.

0

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.

2
  • The source data is from an external source unfortunately.
    – mahemoff
    Jan 15 '16 at 19:55
  • 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.
    – Rick James
    Jan 15 '16 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.