INSERT ... ON DUPLICATE KEY UPDATE with WHERE?

Question

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.

Answer 1

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

Answer 2

This is our final solution, works like a charm!

The insert ignore will make sure that the row exists on both the master and slave, in case they've ever diverted.

The update ... where makes sure that only the most recent update, globally, is the end result after all replication is done.

mysql> desc test; +-------+--------------+------+-----+-------------------+-------+ | Field | Type         | Null | Key | Default           | Extra | +-------+--------------+------+-----+-------------------+-------+ | id    | int(11)      | NO   | PRI | NULL              |       |  | value | varchar(255) | YES  |     | NULL              |       |  | ts    | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |  +-------+--------------+------+-----+-------------------+-------+  mysql> insert ignore into test values (4, "foo", now());     mysql> update test set value = "foo", ts = now() where id = 4 and ts <= now();