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.

Patinous

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

Bunion

I never knew I could access the original table value inside the UPDATE-part. This probably solves my problem (although my old work around is pretty good :)) – Patinous

It may happen that MySQL will complain about 'column in field list is ambiguous' - then you'd need to use: ON DUPLICATE KEY UPDATE daily_events.last_event_id = IF(daily_events.last_event_created_at < VALUES(last_event_created_at), VALUES(last_event_id), daily_events.last_event_id); – Cyanosis

The link posted to thewebfellas.com is dead, sadly. – Floweret

The link posted to thewebfellas.com is now viable – Megargee

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

Patinous

The explanation is in the @lexu answer: two statements, the first is insert ignore, the second is something like update ignore. – Fauch

Looks like the second statement is just doing update test set value = "foo", ts = now() where id = 4 and ts < now() except in a very convoluted way. – Hershelhershell

Yes, you are correct. I've since posting this answer changed the code to be insert ignore... followed by an update .... where ts < now() – Patinous

@AndreasWederbrand could you update your answer accordingly, so others may benefit from your conclusions as well? – Chokefull

Answer 3

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'.

Relume

Answer 4

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

Adamic

Answer 5

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

Eudora

Answer 6

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

Multifoliate

How can insert ignore fail? – Patinous

if you try to insert same unique key insert ignore will fail. – Multifoliate

No, thats what ignore does. It ignores the duplicate key exception. – Patinous