1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Rep Power

    UPDATE Statment with Nested SELECT

    Hi All,

    I am fairly new to MySQL -- my issue is I need to update a Column based on the Column added from a Select statement:
    Is this even possible to do? Here is the Select statement that I run and gives me the values I need. I then have the Update statement that has the two columns I need to match after the Select Statement is run:

    SET @rownum = 0, @rank = 1, @prev_val = NULL;

    SELECT *,@rownum := @rownum + 1 AS row
    , @rank := IF(@prev_val!=Score,@rownum,@rank) AS rank
    , @prev_val := Score AS score
    FROM dbname.tablename
    WHERE idGame = 1
    ORDER BY Score DESC;

    UPDATE dbname.tablename
    SET tablename.Rank = tablename.rank

    Thanks so much!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Toronto Canada
    Rep Power
    check this out -- http://forums.devshed.com/mysql-help...le-932094.html

    Comments on this post

    • TASB agrees : Such a random thing for two people to want to figure out at almost the same time.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo