10
I have 2 tables: tblRider and tbl_SCORE. tblRider has all the information about all the riders (competitors) and in tbl_SCORE are all the riders's scores saved. I want to update a column HalfPipeFinal in tblRider. Standard this column is set on 0, but I want to set it on 1 for the riders with the 20 best scores. (so only the 20 best riders can participate in the final and those who do have a 1 in column HalfPipeRider)
This is my query:
UPDATE tblRider
JOIN tbl_SCORE ON tblRider.riderID = tbl_SCORE.riderID
SET tblRider.HalfPipeFinal = 1
WHERE `gameID` =35
ORDER BY `score` DESC
limit 20;**
If I run this query I get this error: "Incorrect usage of UPDATE and ORDER BY" So I went looking and apparently you can't use update and order by in a join. So I am looking for an other way to write this query without a order by in it, but I can't find it.
All help is appreciated. Thanks