INSERT INTO … ON DUPLICATE KEY UPDATE …. with condition?

Question

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

name	value	lastupdate
a	10	2021-01-01
b	20	2021-02-01
c	30	2021-03-01

Now I have some data to be imported

name	value	lastupdate
a	20	2021-01-01
b	40	2021-01-01
c	60	2021-04-01

The result of the query should be

name	value	lastupdate
a	20	2021-01-01
b	20	2021-02-01
c	60	2021-03-01

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?

Answer 1

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.