Nick Nick - 3 months ago 15
MySQL Question

INSERT ... ON DUPLICATE KEY UPDATE row IF date_added > VALUES(date_added)

How can I

INSERT
and
ON DUPLICATE KEY
update the whole row if a certain condition is met?
I want to update the whole
row
if
VALUES(date_added)
is older then
date_added
from the table.

I've seen this article:

https://thewebfellas.com/blog/conditional-duplicate-key-updates-with-mysql

INSERT INTO daily_events
(created_on, last_event_id, last_event_created_at)
VALUES
('2010-01-19', 23, '2010-01-19 10:23:11')
ON DUPLICATE KEY UPDATE
last_event_id = IF(last_event_created_at < VALUES(last_event_created_at), VALUES(last_event_id), last_event_id),
last_event_created_at = IF(last_event_created_at < VALUES(last_event_created_at), VALUES(last_event_created_at), last_event_created_at);


But this isn't a flexible way to write the statement.

Later Edit:

First, because the condition is evaluated for each field that will be updated, and second, because you have to pay attention to position the line that updates the condition column, in the last position of the statement. Otherwise you poison the
UPDATE
with errors.

To be more exact, I'd want this statement, where I've inverted the two UPDATE lines, to be valid also:

INSERT INTO daily_events(created_on, last_event_id, last_event_created_at) VALUES('2010-01-19', 23, '2010-01-19 10:23:11')
ON DUPLICATE KEY UPDATE
last_event_created_at = GREATEST(last_event_created_at, VALUES(last_event_created_at)),
last_event_id = (CASE WHEN last_event_created_at < VALUES(last_event_created_at) THEN VALUES(last_event_id) ELSE last_event_id END);


This isn't possible because:


An important thing to keep in mind when using this approach is that the order in which you update your fields is very important. I was wrongly under the impression that the updates took place in one mass-assignment after the entire query had been interpreted by MySQL. But they’re not: the assignments happen in the order they appear in the query.

When the
update
is executed with a more recent event, the
last_event_created_at
field will be updated, but the
last_event_id
field won’t. This is because when the second
IF
is evaluated
last_event_created_at
has already been updated so that
last_event_created_at
is equal to
VALUES(last_event_created_at)
. Crazy huh?!

Answer

Your query seems to do what you want, so I don't fully understand the question. You might simplify it by using GREATEST():

INSERT INTO daily_events(created_on, last_event_id, last_event_created_at)
    VALUES('2010-01-19', 23, '2010-01-19 10:23:11')
ON DUPLICATE KEY UPDATE
  last_event_id = (CASE WHEN last_event_created_at < VALUES(last_event_created_at) THEN VALUES(last_event_id) ELSE last_event_id END),
  last_event_created_at = GREATEST(last_event_created_at, VALUES(last_event_created_at));

(The use of CASE is a preference for ANSI standards for equivalent functionality.)

MySQL doesn't offer a way to say "update all of these when this condition is met".

Comments