CODEWITHSUNDEEP
mysqlinsertduplicatesinsert-update
Nick
Nick

Reputation: 331

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?!

Upvotes: 0

Views: 434

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

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".

Upvotes: 1

Related Questions