What's wrong with this update RANK query? It was updated only the first time I ran it, after that the users_tbl_rank stays the same.

$sql = "SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC";


In my table, users_tbl_rank and users_tbl_points are both integers.   



Could anyone tell me what I'm doing wrong here? Thanks in advance.
I created a random sample of 30 rows based on the table above as follows:

DROP DATABASE IF EXISTS sample;
CREATE DATABASE sample;
USE sample
CREATE TABLE users
(id int not null auto_increment,
users_tbl_points int not null,
users_tbl_rank int not null default 0,
primary key (id),key (users_tbl_points));
INSERT INTO users (users_tbl_points) VALUES
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000));
SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
SELECT * FROM users ORDER BY users_tbl_points DESC;


Here is that output

mysql&gt; DROP DATABASE IF EXISTS sample;
CREATE DATABASE sample;
USE sample
CREATE TABLE users
(id int not null auto_increment,
users_tbl_points int not null,
users_tbl_rank int not null default 0,
primary key (id),key (users_tbl_points));
INSERT INTO users (users_tbl_points) VALUES
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000));
SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
SELECT * FROM users ORDER BY users_tbl_points DESC;
Query OK, 1 row affected (0.04 sec)

mysql&gt; CREATE DATABASE sample;
Query OK, 1 row affected (0.00 sec)

mysql&gt; USE sample
Database changed
mysql&gt; CREATE TABLE users
    -&gt; (id int not null auto_increment,
    -&gt; users_tbl_points int not null,
    -&gt; users_tbl_rank int not null default 0,
    -&gt; primary key (id),key (users_tbl_points));
Query OK, 0 rows affected (0.06 sec)

mysql&gt; INSERT INTO users (users_tbl_points) VALUES
    -&gt; (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -&gt; (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -&gt; (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -&gt; (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -&gt; (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -&gt; (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -&gt; (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -&gt; (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -&gt; (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -&gt; (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000));
Query OK, 30 rows affected (0.08 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql&gt; SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
Query OK, 0 rows affected (0.00 sec)

Query OK, 30 rows affected (0.05 sec)
Rows matched: 30  Changed: 30  Warnings: 0

mysql&gt; SELECT * FROM users ORDER BY users_tbl_points DESC;
+----+------------------+----------------+
| id | users_tbl_points | users_tbl_rank |
+----+------------------+----------------+
| 27 |           974504 |              1 |
|  3 |           942712 |              2 |
| 26 |           914442 |              3 |
| 23 |           867887 |              4 |
| 13 |           828882 |              5 |
| 12 |           809094 |              6 |
| 29 |           722458 |              7 |
| 14 |           717128 |              8 |
|  4 |           702457 |              9 |
|  1 |           691932 |             10 |
|  5 |           684147 |             11 |
|  8 |           631802 |             12 |
|  9 |           615874 |             13 |
|  7 |           514378 |             14 |
| 17 |           420973 |             15 |
| 24 |           360579 |             16 |
| 16 |           343592 |             17 |
| 22 |           326285 |             18 |
| 20 |           315427 |             19 |
|  6 |           313363 |             20 |
| 21 |           254513 |             21 |
| 30 |           224710 |             22 |
| 25 |           199236 |             23 |
| 10 |           183963 |             24 |
| 28 |           129194 |             25 |
| 19 |           107540 |             26 |
| 15 |            98995 |             27 |
| 18 |            74092 |             28 |
| 11 |            72197 |             29 |
|  2 |             3702 |             30 |
+----+------------------+----------------+
30 rows in set (0.01 sec)

mysql&gt;


Let me update the scores

mysql&gt; UPDATE users SET users_tbl_points = FLOOR(rand()*1000000);
Query OK, 30 rows affected (0.08 sec)
Rows matched: 30  Changed: 30  Warnings: 0


Let me rerank them

mysql&gt; SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
Query OK, 0 rows affected (0.00 sec)

Query OK, 29 rows affected (0.06 sec)
Rows matched: 30  Changed: 29  Warnings: 0


Let me display them:

mysql&gt; SELECT * FROM users ORDER BY users_tbl_points DESC;
+----+------------------+----------------+
| id | users_tbl_points | users_tbl_rank |
+----+------------------+----------------+
| 28 |           994943 |              1 |
|  7 |           983596 |              2 |
|  4 |           964909 |              3 |
| 24 |           921117 |              4 |
| 14 |           885399 |              5 |
|  8 |           881325 |              6 |
|  3 |           858830 |              7 |
| 11 |           808514 |              8 |
|  1 |           785523 |              9 |
| 26 |           751842 |             10 |
| 22 |           749931 |             11 |
| 21 |           705158 |             12 |
| 25 |           703038 |             13 |
| 27 |           650093 |             14 |
| 15 |           649229 |             15 |
| 10 |           635204 |             16 |
| 23 |           634182 |             17 |
| 16 |           589948 |             18 |
|  9 |           455836 |             19 |
|  2 |           443080 |             20 |
|  6 |           345553 |             21 |
| 13 |           259255 |             22 |
| 20 |           258620 |             23 |
|  5 |           248056 |             24 |
| 18 |           240429 |             25 |
| 19 |           195981 |             26 |
| 30 |           137355 |             27 |
| 12 |           136960 |             28 |
| 29 |            24437 |             29 |
| 17 |             2055 |             30 |
+----+------------------+----------------+
30 rows in set (0.00 sec)

mysql&gt;


Looks like it works to me !!!

CAVEAT

Make sure you have an index on the users_tbl_points column