Using the sql table stored several times in a query

存储架构 2017-12-31

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!

责编内容by:Hello, buddy! (源链)。感谢您的支持!

您可能感兴趣的

Building SQL ConstantCare®: 10% of you have Priori... One of my favorite things about my job is being able to take the pulse of how real companies are man...
SQL SERVER 自动生成 MySQL 表结构及索引 的建表SQL... SQL SERVER的表结构及索引转换为MySQL的表结构及索引,其实在很多第三方工具中有提供,比如navicat、sqlyog等,但是,在处理某些数据类型、默认值及索引转换的时候,总有些不尽人...
Why Don’t I have a Preferred Replica? The client reported that the log file on their main database was growing unusually large, an...
The Tarantool SQL Alpha Tarantool, a Lua application server plus NoSQL DBMS, is now an SQL DBMS too, in alpha version 1.8. I...
Learn AWS With MS-SQL(DB) Using SQL Client Introduction In this article, we will learn about Amazon web service with MS-SQL database usin...