Insert only if new value is the highest

Question

Player 1 has finished a game and got a score of 10. We should save this score only if it's the high score for that user. How could I accomplish this in a single query? I tried this:

INSERT INTO highscore("score", "player") values(10, 1)
WHERE (SELECT MAX(score) as hs FROM highscore WHERE player = 1) < 10;

Thanks

I've already read that. However I don't know what that "select 2, 20" is for? — , May 29 '14 at 23:17
`select 2, 20` -- that is selecting 2 columns with the values of 2 and 20 respectively. In your case, `select 10, 1` would be equivalent, where the first column is the score and the second is the player. — sgeddes, May 29 '14 at 23:37

score 0 · Answer 1 · answered May 29 '14 at 23:24

0

Make uniq index on player. Then make insert update statement with Max () function. This can help if you are using mysql.

something like this: Alter table highscore add uniq key (player); Insert into highscore (player, score) values (1,10) on duplicate update Score = max (score,10)

answered May 29 '14 at 23:24

MichalSv

537
5
10

But this replaces the last high score. I want to have a history of highscores. Im working on a solution with a trigger function before insert. It might work. – May 29 '14 at 23:34
1. why you need it on a single query? 2. why don't you create a procedure instead of a trigger? – Radu Vlad May 29 '14 at 23:37
Trigger is solution. Becareful if you are using replication. – MichalSv May 29 '14 at 23:39
1. To lower the costs. 2.I can't use procedure because the hosting provider does not allow it. – May 29 '14 at 23:42
Insert into highscore (player, score) values (1,10) on duplicate update Score = max (Score,10), history = concat (history,',', Score). But in single query it is realy difficult or stupid :-) – MichalSv May 29 '14 at 23:46

score 0 · Accepted Answer · answered May 30 '14 at 00:00

The ugly thing is that mysql does not support insert into values with where condition, but you can cheat by doing this:

INSERT INTO highscore (score, player)
SELECT 10, 1 FROM dual
WHERE (SELECT max(score) FROM highscore WHERE player = 1) < 10

This will insert no rows if the score is higher than 10, otherwise it will create a row with the values you need. It's also easier than a trigger and i believe it also has a lower cost.

score 0 · Answer 3 · edited May 23 '17 at 10:32

INSERT INTO `max_score` 
  SELECT 77, 7 FROM `max_score` WHERE NOT EXISTS 
  (SELECT * FROM `max_score` WHERE id = 77 AND score >= 7) LIMIT 1;

(77 is the id, and 7 is the score.)

This will insert if there are no values greater than or equal to 7 with and id equal to 77, and will NOT insert if there are values greater than 7.

And with prepared statements and a timestamp:

<?php

$id = 77;
$score = 13;

$date = new DateTime();
$timestamp = $date->format('Y-m-d H:i:s');

$sql = <<<EOT

INSERT INTO `max_score` 
  SELECT ?, ?, ? FROM `max_score` WHERE NOT EXISTS 
  (SELECT * FROM `max_score` WHERE id = ? AND score >= ?) LIMIT 1;

EOT;

$stmt = $mysqli->prepare($sql);
$stmt->bind_param('iisii', $id, $score, $timestamp, $id, $score);
$stmt->execute();

?>

(One caveat: table max_score must have at least one record in it for the SELECT ?, ? FROMmax_score` to return the provided values.)

Inspired by: MySQL: Insert record if not exists in table

Insert only if new value is the highest

3 Answers3