5

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

dynamic
  • 46,985
  • 55
  • 154
  • 231

2 Answers2

7

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) ;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • my col is NOT NULL so i don't have this problem – dynamic Apr 27 '12 at 16:48
  • Yes, because `GREATEST(5, NULL)` will result in `NULL`. So if by any chance you send `INSERT INTO tbl (count, otherID) VALUES (NULL, 'a') ON DUPLICATE KEY UPDATE count = GREATEST(VALUES(count), count) ;` will replace the existing value in the row with `NULL`. ` – ypercubeᵀᴹ Apr 27 '12 at 16:48
  • Ah yes, if the column is `NOT NULL`, the update to Null will fail with error, so **no problem**. – ypercubeᵀᴹ Apr 27 '12 at 16:52
  • Can you please take a look at my [question](http://stackoverflow.com/questions/32154794/how-to-control-the-errors-in-on-duplicate-key-using-if-function), It is related with this answer. – Shafizadeh Aug 22 '15 at 10:11
5

Haven't tested it, but I think this should work.

INSERT INTO tbl (count, otherID) VALUES(2,'a')
ON DUPLICATE KEY 
UPDATE count = IF(VALUES(count) > count, VALUES(count), count)
Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177