Updating user rank

Question

I'm trying to update users' rankings in a single query but it crashes with this error:

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE users SET rank = @r:= (@r+1) ORDER BY score DESC' at line 1

Is there anything wrong with my query?

SET @r=0;UPDATE users SET rank = @r:= (@r+1) ORDER BY score DESC

I got the query from this answer

I'm executing this query on a node.js mysql library.

    db.query('SET @r=0;UPDATE users SET rank = @r:= (@r+1) ORDER BY score DESC', function(err){
        if (err){
            console.log(err);
            return;
        }
        console.log("Ranking updated successfully.");
    });

Where are you executing this statement? Directly in command line or in a programming language? — Yannici, Aug 17 '17 at 13:31

score 0 · Answer 1 · answered Aug 17 '17 at 13:32

0

How about:

SET @r=0;UPDATE users SET rank = (@r+1) ORDER BY score DESC

I'm not sure as why you would want to reassign the var again.

answered Aug 17 '17 at 13:32

Peter M

1,059
8
19

1

Because he wants to have the new value in @r for the next user ;) (rank 1, rank 2, rank 3 ...) – Yannici Aug 17 '17 at 13:32
Didn't work. I don't think it's about the query, it works without `SET @r=0;` but updates rank as NULL. I've updated the question with code. – user234 Aug 17 '17 at 13:37
My bad. Sorry about that. – Peter M Aug 17 '17 at 14:40

score 0 · Answer 2 · answered Aug 17 '17 at 13:44

0

Ok, i've found the problem.

I forgot to set multipleStatements to true as described in here.

answered Aug 17 '17 at 13:44

user234

99
1
1
8

Updating user rank

2 Answers2