All geek questions in one place

How to write a request with attachment, update and order?

I have 2 tables: tblRider and tbl_SCORE. tblRider has all the information about all the riders (competitors), and in tbl_SCORE - all the ratings of the riders. I want to update the HalfPipeFinal column in tblRider. This standard column is set to 0, but I want to set it to 1 for riders with 20 best results. (so only the top 20 riders can participate in the finals, and those who have 1 in the HalfPipeRider column)

This is my request:

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: "Improper use of UPDATE and ORDER BY" Therefore, I went to search and, apparently, you cannot use the update and order in the connection. Therefore, I am looking for another way to write this request without an order in it, but I cannot find it.

All help is appreciated. Thanks

+6
source share
2 answers

In SQL, you cannot have ORDER BY as part of 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;
+11
source

On top of my head and without opening an SQL session, to test this, you can try ...

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

Although IN (subselect) can have unpleasant penalties.

+3
source

Source: https://habr.com/ru/post/944541/

More articles:


All Articles