-1

If a player has earned certain points ,based on the points there are some levels,now if I have the total points of player I want to update his/her level by checking into the table whether the player has exceeded the minimum score limit to attain the next level ? 

level   desc      points
 1      level 1    200
 2      level 2    400
 3      level 3    600

player current points are 350 as soon as he reaches 400 his level should be updated to 2

playerid    playername  level
  1            abc        1
  2            xyz        3
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Swanand Keskar
  • 1,023
  • 1
  • 13
  • 27

2 Answers2

0
update table_name
set level =case when points <350 then 1
                when points >= 350 then 2 
                else 3  
           end
Ankit Agrawal
  • 2,426
  • 1
  • 13
  • 27
  • if I pass points as a parameter then it will stop at 2 even if there are additional levels available because when will evaluate to true when 500 <350 then 1 when 500 >= 350 then 2 when 500 >=400 then 3 else 4 – Swanand Keskar Nov 26 '16 at 05:52
  • @swanandkeskar if you have more that these level than you can create dynamic case statement that will work for you. – Ankit Agrawal Nov 26 '16 at 06:10
0

If you have a table named players with fields id and points and a table named levels with fields level and points than the following query will work

 SELECT players.*,max(levels.level) AS level 
 FROM players 
 LEFT JOIN levels ON players.points >= levels.points 
 GROUP BY players.id
Ralph Ritoch
  • 3,260
  • 27
  • 37
  • Updating from a select is covered in another question http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query – Ralph Ritoch Nov 26 '16 at 04:51