Updating or inserting high scores in SQL
- by Roger Gilbrat
I've been racking my brain over this for the past few days and I'm not sure it's possible, but figured I ask here.
Is it possible for a single SQL statement to update a high score if your score is greater or insert it if your first score?
My Score table has a UserID, Level and Score columns and I like it to follow the following logic:
If your new score is greater than your last score for this Level, then replace it.
If you don't have a score for this Level then add it.
If your score for this Level is less than your highest score for this Level then do nothing.
Is this possible in a single SQL statement or do I have to use two, one to see if you have a new high score and if so, replace it? Each UserID would have only one score in the table for each Level.
I'm using MySQL.