INSERT ... ON DUPLICATE KEY UPDATE with WHERE?

Question

Hi!

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

you could use two insert statements .. since you CAN add a where clause to the select part for the source data.

select two sets of data, one that you will insert with 'on duplicate' and the other will be inserted without 'on duplicate'.

Answer 2

This is our final solution, works like a charm! And just one select.

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());
Query OK, 1 row affected (0.01 sec)

mysql> insert into test select id, "foo", now() from test where id = 4 and ts < now() on duplicate key update value = values(value), ts = values(ts);
Query OK, 2 rows affected (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 0