Using the sql table stored several times in a query

Hello I have a sql query which generates rankings for my database

SET @rownum := 0; 

SELECT
   rank, Total, UserID_ChampID
FROM
   (SELECT
       @rownum := @rownum +1 AS rank, total, UserID_ChampID
    FROM
       Summoner_Champions
    WHERE
       Champion_ID = 0
    ORDER BY
       Total DESC) as result

This returns a correct table like

rank  total   userid_champid
  1     379     566_0

My question is: I want to update the table im drawing this data from

So I want to update is such that this code works

UPDATE Summoner_Champions
SET Rank = result.rank
WHERE Summoner_Champions.UserID_ChampID = result.UserID_ChampID

Sorry if this is a completly newbie question but I couldn’t find a way to make that code work by using subqueries or work arounds

You can update your query using your SELECT
query. See below:

SET @rownum := 0;
UPDATE Summoner_Champions
SET
Rank = (SELECT rank FROM
   (SELECT
       @rownum := @rownum +1 AS rank, total, UserID_ChampID
    FROM
       Summoner_Champions
    WHERE
       Champion_ID = 0
    ORDER BY
       Total DESC) as result)
Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Using the sql table stored several times in a query

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录