How do I insert into a database only if a value has changed?

Question

I need to update (replace) fields in a MySQL database, but only if they have changed.

The table contains an ID, text field and date changed. Users query the data by ID based on the date it was changed. i.e. If the date is before the last the time user queried the data, he doesn't want it.

I would like to change the date in the database only if the text field is different from the existing text field with the same id.

I could query the data, compare the result and only post back if the new data is different, but this is a lot of overhead and the database connection is rather slow, so I am looking for a way to do this in a single query.

Thank you.

Answer 1

You can include a CASE statement in your update query that will set the date field conditionally, like so:

UPDATE MyTable
SET textfield = @newValue,
datefield = (CASE WHEN textfield <> @newValue THEN NOW() ELSE datefield END);

This query "sets" the datefield to the same value it already contains if the value of textfield hasn't been changed.

Answer 2

I think you answered your own question here:

if the text field is different from the existing text field with the same id

in other words, use the MySQL CASE statement to check if the text field is different. If it is, make the update on both the text field and the date. If not, don't make any updates.

Alternatively, you might be able to make this same check in your code, which would involve fewer trips to the database. This might alleviate the slow database connection issue.