How to insert into the database only if the value has changed?

Question

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

The table contains an identifier, a text box, and a modification date. Users request data by identifier depending on the date of its change. those. if the date precedes the last, the user requests data, he does not want this.

I want to change the date in the database only if the text field differs from the existing text field with the same identifier.

I could query the data, compare the result and send messages only if the new data is different, but it is a lot of overhead, and the database connection is rather slow, so I'm looking for a way to do this in a single request.

Thank.

Answer 1

You can include the CASE statement in the update request, which will set the date field conditionally, for example:

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

This query "sets" datefieldto the same value that it already contains if the value has textfieldnot been changed.

Answer 2

I think you answered your question:

if the text field differs from the existing text field by the same identifier

in other words, use the MySQL CASE statement to check if the text field is different. If so, update both the text box and the date. If not, do not make any updates.

, . .

Answer 3

A simple way that 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;

topic: MySQL Trigger after upgrade only if the row has changed

Answer 4

I was mistakenly accepted by the @value tag that you used and the order is wrong, you should try the following:

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