MySQL Conditional Insert on Duplicate

Question

I have an example table with 3 fields where adhex is the primary key. At the moment I am using ASP and a recordset and it is VERY slow and need to move to a PHP and faster system. I have been recommended the INSERT...ON DUPLICATE KEY UPDATE which works great!

However I want the update to be conditional and cannot work it out!

So far I have the below which doesn't work at all!

What I need to happen is on the update ONLY update the reg and mtime if the mtime in the values is > the one in the table. I'm sure this is a simple one but I can't work out the conditional part even looking at some examples.

INSERT INTO testTable (adhex,reg,mtime) 
VALUES ('00B0BA','reg-1','1294129605') 
ON DUPLICATE KEY UPDATE reg='ZsS-SLD' 
  CASE 
    WHEN mtime < values(mtime) THEN values(mtime) 
  END

Answer 1

That's not how CASE works. It returns a value, not a piece of SQL code.

Insert into testTable (adhex,reg,mtime)
VALUES ('00B0BA','reg-1','1294129605')
ON DUPLICATE KEY UPDATE reg=/*'ZsS-SLD'*/ CASE -- Note the commented out value
    WHEN mtime < values(mtime) THEN values(mtime)
    -- Notice there's not ELSE: you'll get NULL if condition is not matched
END

Answer 2

Take a closer look at the syntax for INSERT ... ON DUPLICATE. After ON DUPLICATE KEY UPDATE comes a sequence of column = expression statements. Try something like:

INSERT INTO testTable (adhex,reg,mtime) 
VALUES ('00B0BA','reg-1','1294129605') 
ON DUPLICATE KEY UPDATE reg=IF(mtime < VALUES(mtime), 'ZsS-SLD', reg), 
  mtime=IF(mtime < VALUES(mtime), VALUES(mtime), mtime)