CODEWITHSUNDEEP
mysqlsql
dynamic
dynamic

Reputation: 48101

on duplicate key update with a condition?

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

Upvotes: 7

Views: 5956

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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

Upvotes: 10

Eric Petroelje
Eric Petroelje

Reputation: 60498

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)

Upvotes: 5

Related Questions