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?

In your sample result, is the "lastupdate" "2021-04-01" or not (e.g. shall it be updated too, or only "value"?) Also, on duplicate key requires, well, a key. Can you specify your primary key? (It has to be "name" to work as you want it to, so can you confirm that that's your primary key?) — Solarflare, Nov 12 '21 at 17:24

score 3 · Accepted Answer · answered Nov 12 '21 at 17:39

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.

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

1 Answers1