I am still wondering if there is something like a conditional on duplicate update in MySQL 5.7

I have a table which is updated by different sources.

Let’s assume I have a table

CREATE TABLE t 
(
    name VARCHAR(100),
    value INT,
    last update DATETIME
)

I have 3 rows

Now I have some data to be imported

The result of the query should be

Can this be done by one insert query or must I check first if the last update of the existing data in the table is newer then the date of the import data?

CodePudding user response：

Assuming that name is the PRIMARY KEY of the table or is defined as UNIQUE, you can use a CASE expression:

INSERT INTO t (name, value, lastupdate) VALUES
  ('a', 20, '2021-01-01'),
  ('b', 40, '2021-01-01'),
  ('c', 60, '2021-04-01')
ON DUPLICATE KEY UPDATE
value = CASE WHEN VALUES(lastupdate) >= lastupdate THEN VALUES(value) ELSE value END;

See the demo.

Note that (from INSERT ... ON DUPLICATE KEY UPDATE Statement):

The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.20, and is subject to removal in a future version of MySQL. Instead, use row and column aliases, as described in the next few paragraphs of this section.

So, if your version of MySql is 8.0.20 it is recommended to use an alias instead of VALUES():

INSERT INTO t (name, value, lastupdate) VALUES
  ('a', 20, '2021-01-01'),
  ('b', 40, '2021-01-01'),
  ('c', 60, '2021-04-01') AS new
ON DUPLICATE KEY UPDATE
t.value = CASE WHEN new.lastupdate >= t.lastupdate THEN new.value ELSE t.value END;

See the demo.