Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.5k views
in Technique[技术] by (71.8m points)

mysql - 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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...