modifying a mysql table to show ranking based on column value

Question

I'm working on some code in php/MySql and I need to update a field called scorerank based on the score value. I found a previous question here that addressed what I was looking for:

Update the rank in a MySQL Table

I modified their example to work with my database. I also added a condition so that I only modified columns that had a specific tourneyid and tableid. I used a unique index variable in the inner join to id which row to update - I think I did it right, but I get the following error:

1248 - Every derived table must have its own alias

Here's my code. Can anybody spot the error(s)?

UPDATE   tscores
JOIN     (SELECT    p.userid,p.scoreid,
                    IF(@lastPoint <> p.score, 
                       @curRank := @curRank + 1, 
                       @curRank)  AS rank,
                    IF(@lastPoint = p.score, 
                       @curRank := @curRank + 1, 
                       @curRank),
                    @lastPoint := p.score
          FROM      tscores p
          WHERE     (p.tourneyid=1 and p.tableid=2)
          JOIN      (SELECT @curRank := 0, @lastPoint := 0) 
          ORDER BY  p.score DESC

         ) ranks ON (ranks.userid = tscores.userid)
SET      tscores.scorerank = ranks.scorerank
WHERE    tscores.scoreid = p.scoreid 

Here's the table I'm working with:

CREATE TABLE IF NOT EXISTS `tscores` (
  `scoreid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tourneyid` int(10) unsigned NOT NULL,
  `tableid` int(10) unsigned NOT NULL,
  `userid` int(10) unsigned NOT NULL,
  `score` int(16) unsigned NOT NULL DEFAULT '0',
  `scorestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `scoreround` int(3) unsigned NOT NULL DEFAULT '0',
  `scoregroup` int(3) unsigned NOT NULL DEFAULT '0',
  `scorerank` int(4) unsigned DEFAULT NULL,
  PRIMARY KEY (`scoreid`),
  KEY `tourneyid` (`tourneyid`,`tableid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=62 ;

Answer 1

In your last line you are trying to use an alias that is inside a subquery.. see the fix below

WHERE    tscores.scoreid = ranks.scoreid 

Answer 2

one more attempt: try putting an alias (name or a leTTER) after this (i put mm)

JOIN      (SELECT @curRank := 0, @lastPoint := 0) mm

Answer 3

not that but swap the places of where and join join comes after from clause not after where
change this ..

WHERE     (p.tourneyid=1 and p.tableid=2)
   JOIN      (SELECT @curRank := 0, @lastPoint := 0) 

to this

JOIN      (SELECT @curRank := 0, @lastPoint := 0) 
WHERE     (p.tourneyid=1 and p.tableid=2) 

and keep previous fixes as well

Answer 4

Here is a revised version of your query:

UPDATE   tscores JOIN
         (SELECT    p.userid, p.scoreid,
                    IF(@lastPoint <> p.score, 
                       @curRank := @curRank + 1, 
                       @curRank)  AS rank,
                    @lastPoint := p.score
          FROM      tscores p CROSS JOIN
                    (SELECT @curRank := 0, @lastPoint := 0) const
          WHERE     (p.tourneyid = 1 and p.tableid = 2) 
          ORDER BY  p.score DESC
         ) ranks
         ON tscores.scoreid = p.scoreid 
    SET      tscores.scorerank = ranks.rank;

Here are changes:

1. Made the join condition on scoreid. This is the primary key on tscores so that is all that is needed.
2. Removed the where clause and the redundant condition on userid.
3. Removed the second if() statement, which also incremented @curRank.
4. Changed the JOIN to a CROSS JOIN. Without an on clause, you should use CROSS JOIN (even though MySQL does allow JOIN there).
5. Added a table alias for the assignment subquery.
6. Changed the set clause to refer to rank instead of scorerank.
7. Moved the assignment subquery before the where clause.