MySQL Update if column value is greater than previous or insert if one doesnt exist

Question

I take in a userId, leaderboardId, and Score and need to insert if that user doesn't have a score for that leaderboard or update if it does have a score and the new score is larger.

My question is what is the SQL statement needed to accomplish the above.

I've looked into insert and on duplicate but that only seems to work for unique keys where in this example there can be multiple of the same userIds as long as they are in different leaderboards and vice versa.

Thanks

Solved! Edit:

thanks everyone here is what I did to make it work!

UNIQUE KEY 'newKey' (userId, leaderboardId)

insert into score (UserId, LeaderboardId, Score) values(1,5,100) ON DUPLICATE KEY UPDATE score = greatest(Score, values(Score))

did you tried something, can you post your code in your question — Meenesh Jain, Aug 30 '15 at 05:54
You can create a `UNIQUE KEY` on multiple columns - ie. `UNIQUE KEY \`newKey\` (userId, leaderboardId)`. So now a `INSERT ... ON DUPLICATE UPDATE ...` query will work see https://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html or http://stackoverflow.com/a/635943/689579 — Sean, Aug 30 '15 at 05:57
He will update, if the new score is greater than the stored score. So the update shouldn't go every time. — dev.null, Aug 30 '15 at 06:04

Drew · Answer 1 · 2015-08-30T06:33:31.700

this is a cut and paste from one of my other answers, I will tweak it for your category thing, but bear with it until then:

Schema:

CREATE TABLE leaderBoard
(   id int AUTO_INCREMENT primary key,
    userID int not null,
    leaderBoardID int not null,
    score int not null,
    UNIQUE KEY `combo_thingie1` (userID,leaderBoardID)  -- unique composite
) ENGINE=InnoDB auto_increment=150;

Tests:

insert leaderBoard (userID,leaderBoardID,score) values (113,1,0) 
on duplicate key update score=greatest(0,score);

insert leaderBoard (userID,leaderBoardID,score) values (113,2,0) 
on duplicate key update score=greatest(0,score);

select * from leaderBoard;
+----+--------+---------------+-------+
| id | userID | leaderBoardID | score |
+----+--------+---------------+-------+
|  1 |    113 |             1 |     0 |
|  2 |    113 |             2 |     0 |
+----+--------+---------------+-------+

insert leaderBoard (userID,leaderBoardID,score) values (113,2,555) 
on duplicate key update score=greatest(555,score);

select * from leaderBoard;
+----+--------+---------------+-------+
| id | userID | leaderBoardID | score |
+----+--------+---------------+-------+
|  1 |    113 |             1 |     0 |
|  2 |    113 |             2 |   555 |
+----+--------+---------------+-------+

insert leaderBoard (userID,leaderBoardID,score) values (113,2,444) 
on duplicate key update score=greatest(444,score); -- ignores lower score

select * from leaderBoard;
+----+--------+---------------+-------+
| id | userID | leaderBoardID | score |
+----+--------+---------------+-------+
|  1 |    113 |             1 |     0 |
|  2 |    113 |             2 |   555 |
+----+--------+---------------+-------+

no if you try to submit a lower score it just wont accept it — qoznk, Aug 30 '15 at 06:26

score 1 · Answer 2 · answered Aug 30 '15 at 06:02

Try this, if you got null result (= no rows) from this:

select score from table
where userid = 42
and leaderboardId = 2001
and score is not null;

then there are no score and you can insert your datas. Otherwise you have to check, if your new score is greater as your result value, then you can update. Otherwise you have nothing to do.

MySQL Update if column value is greater than previous or insert if one doesnt exist

2 Answers2

Schema:

Tests: