如果值大于当前值，则更新MySQL [英] MySQL update if value is greater than that current value

问题描述

我将继续发布可怕的MySQL代码:

I'm going to go ahead and post my scary MySQL code:

INSERT INTO monthlystats (id, server, time, uptime, players, rank) 
VALUES (09126, 6, 0912, 302, 0, 1) 
ON DUPLICATE KEY UPDATE uptime = if(302 > uptime, 302, uptime), if(0 > players, 0, players), if(1 > rank, 1, rank)

好的，所以让我解释一下我想做什么.

Ok, so let me explain what I'm TRYING to do.

1. "id"具有唯一索引.如果该索引尚不存在，我想插入这些值.
2. 如果有重复的键，我想改为更新3个字段中的每个字段，只有在新值大于表中当前值的情况下.

看来我做错了什么，坦率地说，我完全不知道该怎么做.我尝试使其变好，但结果真是一团糟.

It looks like I'm doing something terribly wrong, and I quite frankly have NO idea what to do. I tried making it good, but it turned out to be a horrible mess.

我希望有人可以引导我朝正确的方向发展，并帮助我学习(以及其他尝试学习的人)如何做这样的事情.谢谢.

I hope that somebody can lead me in the right direction, and help me learn (and anyone else that is trying to learn) how to do things like this. Thank you.

推荐答案

您的查询似乎还不错，但您可以为了清楚起见对其进行改进:

Your query seems fine but you could improve it for clarity:

INSERT INTO monthlystats (id, server, time, uptime, players, rank) 
  VALUES (09126, 6, 0912, 302, 0, 1) 
ON DUPLICATE KEY UPDATE 
  uptime = GREATEST(uptime, VALUES(uptime)), 
  players = GREATEST(players, VALUES(players)),
  rank = GREATEST(rank, VALUES(rank))

这篇关于如果值大于当前值，则更新MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！