问题描述
我有一个带有3个字段的示例表，其中adhex是主键.目前，我正在使用ASP和一个记录集，它非常慢，需要迁移到PHP和更快的系统.我被推荐使用INSERT ... ON重复键更新，效果很好！
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!
我需要做的是更新，如果值中的mtime>表中的那个，则仅更新reg和mtime.我敢肯定这是一个简单的例子，但是即使看一些例子，我也无法解决条件部分.
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
推荐答案
仔细研究 INSERT ... ON DUPLICATE . ON DUPLICATE KEY UPDATE之后是一系列 column = expression 语句.尝试类似的东西:
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)
这篇关于MySQL条件插入重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！