CODEWITHSUNDEEP
mysql
neuzehie
neuzehie

Reputation: 152

MySQL updating row number

I'm trying to copy calculated column values into the correct column of my table but when trying a second update, it doesn't recognize the calculated field.

This is my select:

select @rownum:=@rownum+1 Rank, p.* 
from Poule p, 
    (SELECT @rownum:=0) r 
WHERE p.Activity LIKE 'BallgameOne' 
order by TotalPoints desc

This results in a table with the rownumber (rank) and all columns of the Poule table. One of those columns in the Poule table is called Position.

I would like to copy the values of column Rank into column Position. Because the position got lost on an update. So I would like to do an update based on the returned select.

Is it possible to do so?

Upvotes: 0

Views: 89

Answers (1)

Barmar
Barmar

Reputation: 781721

Join the table with the query that returns the ranks.

UPDATE Poule AS p1
JOIN (select @rownum:=@rownum+1 Rank, p.id
        from Poule p, 
            (SELECT @rownum:=0) r 
        WHERE p.Activity LIKE 'BallgameOne' 
        order by TotalPoints desc) AS p2
ON p1.id = p2.id
SET p1.Position = p2.Rank

Upvotes: 2

Related Questions