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.

Can't you just do the update regardless of the data being changed or not? That's just one query. — Matteo Riva, Sep 23 '10 at 18:51

score 3 · Answer 1 · answered Sep 23 '10 at 18:51

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.

score 0 · Answer 2 · edited May 23 '17 at 12:01

0

Easy way which may not have existed when this question was asked:

CREATE TRIGGER `TimeTrigger` BEFORE UPDATE ON `MyTable` 
FOR EACH ROW
BEGIN
 SET NEW.lastUpdate = (CASE WHEN NEW.text <> OLD.text THEN NOW() ELSE NEW.lastUpdate END);
END;

Related: MySQL Trigger after update only if row has changed

edited May 23 '17 at 12:01

Community

1
1

answered May 09 '14 at 01:11

npjohns

2,218
1
17
16

score 0 · Answer 3 · answered Apr 13 '15 at 15:05

0

I was mistaken by the @value tag you used and the order is wrong, you should try someting like :

UPDATE my_table
SET
    updated = (CASE WHEN foo <> 'new_value' THEN NOW() ELSE updated END),
    foo = 'new_value'
WHERE id = 11;

answered Apr 13 '15 at 15:05

Rybus

651
6
15

score 0 · Answer 4 · answered Sep 23 '10 at 18:54

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.

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

4 Answers4

Linked