mySQL updating but also not updating

Question

I am writing in mySQL/PHP, but have this problem I cannot get my head around. This one PHP script contains two SQL statements. What I am trying to do is update a sports league table (a SQL table called tblrank - which contains many league tables, separated by TableID), and to indicate if the team has gone up or down since the last time it was updated. This first bit of code is bit clunky and probably could've been written better (I could do it in about 6 lines in MSSQL), it works by counting the number of teams that are ranked lower than it, then adding one. It appears to work... sort of, as I will explain later.

update tblrank AS r
        set Rank = 1 + (select count(*) from 
                             (select r2.teamID
                              from tblrank r2
                                   inner join tblrank r3
                              where r3.TableID = r2.TableID and r3.TableID = $tableid
                              and (r3.Points > r2.Points
                                  or (r3.Points = r2.Points and r3.TieBreaker > r2.TieBreaker))) as duh
                        where duh.teamID = r.teamID 
                        and duh.TableID = r.TableID 
                        and r.TableID = $tableid

Then, this bit of code is run to pick the image to display.

update tblrank
set image = case when Rank < LastRank then 'up.png'
        when Rank > LastRank then 'down.png'
        else 'nomove.png' end
where TableID = $tableid

Now if I run this for $tableid = 1, it works just fine. But, if I run it for $tableid = 2, then all Ranks in the whole of tblRank are set to 1. (Before running it for $tableid = 2, all the ranks are 1, except for records where TableID =1 ). This is obviously not what I want.

Both statements are inside a if(mysql_query($sql)) conditional, so I can verify if they have executed or not.

I use MS SQL a lot more than mySQL so I'm not an expert - can anyone help me with this as I'm baffled! I have checked that both chunks of code do execute. There is no other SQL executed between the two.

Answer 1

Well, I've fixed it, I'm not sure what was wrong, but I've split the update statement up, with a temporary table called rankcount being created and populated first. I did this to make it easier to see what was going on.

create temporary table rankcount (TableID int, UserID int)");
   insert into rankcount (TableID, UserID)
            select r2.TableID, r2.UserID
                from tblRank r2
                                inner join tblRank r3
            where r3.TableID = r2.TableID and r3.TableID = $tableid
            and (r3.Points > r2.Points
                   or (r3.Points = r2.Points and r3.TieBreakerOne > r2.TieBreakerOne))

Then this works...

update userEntryTableRank r
        set Rank = 1 + (select count(*) from rankcount rc
                        where rc.UserID = r.UserID and rc.TableID = r.TableID)
        where r.TableID = $tableid

:)

Answer 2

Here's how I'd do it. First initialize all the Rank values to zero.

UPDATE tblrank SET Rank = 0;

UPDATE tblrank r1 JOIN tblrank r2 ON r1.TableID = r2.TableID
SET r1.Rank = r1.Rank + 1
WHERE r1.Points > r2.Points 
  OR (r1.Points = r2.Points AND r1.TieBreaker > r2.TieBreaker)

The join naturally matches each row r1 to the set of rows r2 with the same table and team, and a lower score. Then it increments Rank by 1 for each of these matching rows.