SQL update

Posted on 2009-04-28
Medium Priority
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 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24250212
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses
Course of the Month17 days, 5 hours left to enroll

862 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