INSERT ... ON DUPLICATE KEY UPDATE with WHERE?

mysql, replication

Solution

I suggest you to use IF() to do that.

Refer: 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);

Problem

I'm doing a `INSERT ... ON DUPLICATE KEY UPDATE` but I need the update part to be conditional, only doing the update if some extra condition has changed. However, `WHERE` is not allowed on this `UPDATE`. Is there any workaround for this? I can't do combinations of INSERT/UPDATE/SELECT since this needs to work over a replication.

Original source