How to write a query with join, update and order by?

Question

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

Answer 1

In SQL you can't have an ORDER BY as part of the UPDATE itself. But you can make the filter a subquery, give it an alias and join it...

UPDATE tblRider r
JOIN 
(
    SELECT riderID 
    FROM tbl_SCORE
    WHERE gameID = 35
    ORDER BY score DESC
    LIMIT 20 
) s
ON r.riderID = s.riderID
SET r.HalfPipeFinal = 1;

Answer 2

Off the top of my head and without having a SQL session open to test this you could try...

UPDATE tblRider  
SET HalfPipeFinal = 1 
WHERE riderID 
IN 
(
  SELECT s.riderID 
  FROM tbl_SCORE
  ORDER BY s.score 
  DESC limit 20
)

Though IN (subselect) can have unpleasant performance penalties.