Updating only certain fields on a database row

Question

I have data that I want to insert in a database. Sometime a piece of data is already in the database, so I just want to update the fields that changed.

For example, if I insert these rows one after the other:

id | field1 | field2 | field3
1 | foo | NULL | bar
1 | baz | hello | NULL

I want to end up with:

id | field1 | field2 | field3
1 | baz | hello | bar

I use this query:

INSERT INTO `device` (`device_id`, `model`, `manufacturer`, `version`, `vendor_id`, `osd_name`)
VALUES (....) 
ON DUPLICATE KEY UPDATE `version` = IFNULL(VALUES(`version`), `version`), `vendor_id` = IFNULL(VALUES(`vendor_id`), `vendor_id`), `osd_name` = IFNULL(VALUES(`osd_name`), `osd_name`)

Note that not all fields have to be updated. Is this the best way to do it?

Answer 1

I think it is.

What you are asking for is known as UPSERT - a portmanteau of UPDATE and INSERT. Some databases support UPSERT intrinsically, but some don't. A quick search for "MySQL UPSERT" suggests that MySQL does not support an UPSERT keyword or the concept directly. However, as you discovered, the ON DUPLICATE KEY construct will simulate it.

That seems the best way to do it in MySQL. (In SQL Server you would probably use MERGE.)