SQL update

Posted on 2009-04-28
Last Modified: 2013-12-12
I have an MySQL database with a table "players".

One record:
playerid: 1
quality: 90
round: 1
gain: 0

Another record:
playerid: 1
quality: 92
round: 10
gain: 0

I have a couple of thousand playerid's
Each playerid has (for now) two records; one for round=1 and one for existing round (10 in the example).

I need some update sentence that goes though all my playerids where round=10 (existing round) and alter gain to last quality subtracted first quality, hence sets gain=2 in the last of the two records...

This is how I want my second record to look:
playerid: 1
quality: 92
round: 10
gain: 2   <- different

Question by:kvaade
    1 Comment
    LVL 142

    Accepted Solution

    this should do
    select t.playerid, t.quality round_1_quality, c.quality round_current_quality, c.quality - t.quality as gain
      from yourtable t
      left join yourtable c
        on t.playerid = c.playerid
       and c.round = 10
     where t.playerid = 1
       and t.round = 1 

    Open in new window


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now