I am a French designer and I have a SQL problem. My MySQL server is in v5.6 and i ask for help because I have a syntax problem. I think you can bring me a solution.
I have a data base called "scores":
| id | pseudo | tempsstage1mili | rangstage1 |
|---|---|---|---|
| 1 | pseudo1 | 20100 | --- |
| 2 | pseudo2 | 16900 | --- |
and I ask this to MySQL :
SELECT pseudo,
@curRank := @curRank + 1 AS rank
FROM scores p, (SELECT @curRank := 0) r
ORDER BY tempsstage1mili;
With this command, MySQL return this :
| pseudo | rank |
|---|---|
| pseudo2 | 1 |
| pseudo1 | 2 |
At this step it's perfect but now, I want to Update my table called "scores" with the result. The column I have to Update is called "rangstage1" with the column "rank"
I want to get that :
| id | pseudo | tempsstage1mili | rangstage1 |
|---|---|---|---|
| 1 | pseudo1 | 20100 | 2 |
| 2 | pseudo2 | 16900 | 1 |
Could you help me ? Please ? You are hope !
Thank you !
You can turn this into an update:
SET @curRank = 0;
UPDATE scores
SET rankstage1 = (@curRank := @curRank + 1)
ORDER BY tempsstage1mili;
After some tries I think this will help:
UPDATE scores s
INNER JOIN
( select i.pseudo, i.rank from (SELECT pseudo,
@curRank := @curRank + 1 AS rank
FROM scores p, (SELECT @curRank := 0) r
ORDER BY tempsstage1mili ) i ) a
ON s.pseudo = a.pseudo
SET s.rangstage1 = a.rank ;
Please try and give me feedback, is it working ?