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.

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

Problem

• AWUpsertCondy does not want the insert query to fail if MySQL detects duplicate primary key
• MySQL does not support conditional WHERE clause with ON DUPLICATE KEY UPDATE

Solution

• MySQL supports conditional clause with the IF() function
• Here we have a simple conditional to update only those items with userid less-than 9

INSERT INTO zzdemo_table02 (lname,userid) SELECT lname,userid FROM( SELECT lname,userid FROM zzdemo_table01 ) as tt01 ON DUPLICATE KEY UPDATE userid=IF(@doupdate:=IF( (tt01.userid < 9) , True, False), tt01.userid, zzdemo_table02.userid) ,lname=IF(@doupdate, tt01.lname , zzdemo_table02.lname ) ; 

Pitfalls

• We introduce a MySQL variable @doupdate in order to flag whether or not the UPDATE row meets the condition. Then we use that same variable for all the database columns we use in the UPDATE statement
• In the first conditional we both declare the variable and determine whether the conditional applies. This approach is arguably more cumbersome than a WHERE clause

See also

• MySQL copy column withtout conditional

table php_lock:
name:idString, locked:bool, time:timestamp, locked_by:string
values to insert or update
1, CURRENT_TIMESTAMP, 'script'
where name='wwww' AND locked=2

INSERT INTO `php_lock` (`name`, locked, `time`, `locked_by`) (SELECT * FROM (SELECT `name`,1,CURRENT_TIMESTAMP, 'script' FROM `php_lock` WHERE `name`='wwww' AND `locked`=2 UNION ( SELECT 'wwww',1 ,CURRENT_TIMESTAMP, 'script') ) AS temp LIMIT 1) ON DUPLICATE KEY UPDATE locked=VALUES(locked), `time`=VALUES(`time`), `locked_by`=VALUES(`locked_by`); 

On duplicate key do not allow us to use where clause, so there are two alternative to achieve the same.

1. If you know most of the time you will get the duplicate key then

   a. Update the table first using update query and where clause b. If update fails then insert the record into table using insert query

2. If you know most of the time you are going to insert into table then

   a. Insert the record into table using insert ignore query - what it does is actually ignore the insert if duplicate key found b. If insert ignore fails then update the record using update query

For reference of insert ignore click here