CODEWITHSUNDEEP
mysql
smith
smith

Reputation: 5461

update the ranking in mysql database

I have the follwoing mysql table called user 

 user  email            count  ranking
 sss   [email protected]    111     0
 ss    [email protected]    11      0
 s     [email protected]       1       0

I try to use follwing mysql qyery to update the ranking

    SET @r=0; UPDATE table user SET ranking= @r:= (@r+1) ORDER BY count ASC;

but it give me errors, I don't know where I did wrong, any one could help me with that? thanks a lot!

errors:

       SQL query: 

 UPDATE TABLE user SET ranking = @r := ( @r +1 ) ORDER BY count ASC ;



  MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to    your MySQL server version for the right syntax to use near 'table user SET ranking= @r:= (@r+1) ORDER BY count ASC' at line 1

Upvotes: 0

Views: 145

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270727

TABLE is a MySQL reserved keyword. Enclose it in backquotes when using it as an identifier, but in this case, it is unnecessary and should be removed.

SET @r=0; UPDATE user SET ranking= @r:= (@r+1) ORDER BY count ASC;

Note that 99% of the time, the error message will point exactly to the character or word in the query causing problems. Look to the first word after the ' in the error to start narrowing your problem.

> for the right syntax to use near 'table

Upvotes: 2

Related Questions