some help please

[advo]

hi  i have been having a issue that for life of me just isnt sitting in my head right

 

i have a table called `ranks` with a persons `userID` and various ranks inside it like income whatnotthis table holds there main rank under field `rank`

 

there userinformation is help in `userdeatials` i added a fields called `glorypoints`

 

now what im trying to do is if say there rank is below between 1- 10 give them 2 glory points if 11-50 give them 1

and those above that get nothing

 

now this is what i have and it dose not update the proper parts of table ie rank 1-50 every one is getting them 

 

this is the code im using\

 

UPDATE userdetails SET glorypoints = glorypoints+'2' WHERE rank > 11 AND rank < 50 AND ranks.userID=userdeatils.ID

 

 

[fenway]

This best handed with CASE.

[advo]

what do you mean CASE please explain am i doing it right or wrong some one help please

[Simmo]

you are refering to a table rank  "AND ranks.userID=userdeatils.ID" but you have not have joined it

 

so off the top of my head:

 

UPDATE userdetails Inner Join rank On rank.userID = userdeatils.ID

SET glorypoints = glorypoints+'2' WHERE rank > 11 AND rank < 50 AND ranks.userID=userdeatils.ID

 

 

 

 

[fenway]

what do you mean CASE please explain am i doing it right or wrong some one help please

CASE

WHEN rank > 11 AND rank < 50 THEN glorypoints = glorypoints + 1

WHEN rank > 0 AND rank < 12 THEN glorypoints = glorypoints + 2

END CASE

 

...etc.