Reset SQL variable inside SELECT statement

Question

I am trying to number several rows in a bridge table with a single UPDATE / SELECT statement using a counter variable @row. For instance:

UPDATE teamrank JOIN (SELECT @row := @row + 1 AS position, name FROM members) 
       USING(teamID, memberID) SET rank = position

Is something like this possible or do I need to create a cursor? If this helps, I use MySQL 5.

Answer 1

- ALWAYS initialize user variables, by default they are NULL.

SET @prevID:=@runSum:=0;

UPDATE teamrank t -- one row per team?
  JOIN members  m USING ( teamID ) -- multiple rows per team?
   SET rank =
IF( @prevID != m.teamID, /* Capture when a teamIDs changes */
    ( @runSum := m.rank ) + ((@prevID := m.teamID)*0), /* reset both @runSum and @prevTeam */
    ( @runSum := @runSum + m.rank) /* increment running sum */
  )
-- It is important to have proper sequencing, so to reset @runSum when a teamID changes.
ORDER BY t.teamID 
;

I made the assumption that the logic needed to complete a team rank is the sum of the ranks for individuals in a team.

The same technique allows you to execute any current amount or counter that should be reset when the "group" changes.

- J -

Answer 2

() , :

UPDATE teamrank 
   JOIN (
      SELECT @row := @row + 1 AS position, name 
         FROM members, 
            (SELECT @row := 0) AS ObligatoryAlias
   ) USING(teamID, memberID) 
SET rank = position;