Insert. .. on Duplicate Key Update with Where?
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...
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.
6 Answers
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);
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();
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'.
Overview
- AWUpsertCondy wants to change BEFORE into AFTER
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
