CODEWITHSUNDEEP
mysql
Jacob Brunson
Jacob Brunson

Reputation: 1482

MySQL update if value is greater than that current value

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" has a unique index. If that index doesn't already exist, I want to insert those values.
  2. If there's a duplicate key, I want to instead UPDATE each of 3 fields, only IF the new values are greater than the ones currently in the table.

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.

Upvotes: 20

Views: 17672

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

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

Upvotes: 39

zerkms
zerkms

Reputation: 254934

You could avoid a bit of duplication using VALUES function. And other things are just good in your query

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

Upvotes: 6

Related Questions