有条件的重复密钥更新? [英] on duplicate key update with a condition?

问题描述

我有类似的东西:

INSERT INTO tbl (count,otherID) VALUES (2,'a') ON DUPLICATE KEY UPDATE count = 2

仅当新值大于当前值时，我才想更新计数.因此，假设已经有一个记录，其计数为:4，otherID为:"a"，表示不应触发ON DUPLICATE KEY UPDATE count = 3

I would like to update count only if the new value is greater than the current value. So let's say there is already a record with count: 4 and otherID: 'a' that ON DUPLICATE KEY UPDATE count = 3 should not be triggered

我该如何实现?

我可以使用吗? ... UPDATE count = IF (NEWVALUE > count) NEWVALUE else count

推荐答案

另一个选项:

INSERT INTO tbl (count, otherID) 
  VALUES (2, 'a') 
ON DUPLICATE KEY UPDATE 
  count = GREATEST(VALUES(count), count) ;

警告::如果count的传递值是NULL(而不是2)，则此操作将失败.它将使用NULL更新该列.因此，最好使用IF()或CASE子句.

Warning: This will fail if the passed value for count is NULL (instead of 2). It will update the column with NULL. So, it's better to use the IF() or a CASE clause.

除非您更喜欢(优雅……):

Unless you prefer the (there goes the elegance ...):

ON DUPLICATE KEY UPDATE 
  count = GREATEST(COALESCE(VALUES(count), count), count) ;

这篇关于有条件的重复密钥更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！