on duplicate key update with a condition?

mysql, sql

Solution

Another option:

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

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

Problem

I have something like: ``` INSERT INTO tbl (count,otherID) VALUES (2,'a') ON DUPLICATE KEY UPDATE count = 2 ``` 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 How can i achive this? can i use if? `... UPDATE count = IF (NEWVALUE > count) NEWVALUE else count`

Original source