SQL update

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

Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.