Update column from another table based on same ID
-
Hi All, My head is just at bursting point today trying to solve this problem. I have 2 tables, one table contains statistical information that I need to sum into a total and then update another table column with it. Both tables share an ID, Playerid for example.
Table 1 - PlayerTable contains aprox 10,000 records and looks at little like this
CREATE TABLE Players (
[Ident_player] [int] IDENTITY (1, 1) NOT NULL ,
[playerid] [int] NULL ,
[totalapps] [int] NULL ,
[totalgoals] [int] NULL ,
[careerapps] [int] NULL ,
[careergoals] [int] NULL) ON [PRIMARY]
Tabel 2 - SeasonStats contains the statistical information that I require
CREATE TABLE PlayersStats (
[Ident_Stats] [int] IDENTITY (1, 1) NOT NULL ,
[playerid] [int] NULL ,
[seasonid] [int] NULL ,
[apps] [int] NULL ,
[goals] [int] NULL) ON [PRIMARY]
The ststistics table contain all appearences and goals for a players current and previous seasons. What I need to do is to update table1.totalapps with a sum of table2.apps and also table1.totalgoals with a sum of table.goals I just simply cannot get the joins correct to make this work. All I end up with is the same total of all goals and apps from table2 showing in the total columns of table 1. This is driving me nuts!
UPDATE players
SET totalapps = (SELECT Sum(apps) FROM PlayerStats INNER JOIN Table1 ON playerStats.playerid = players.playerid),
totalgoals = (SELECT Sum(goals) FROM PlayerStats INNER JOIN Table1 ON playerStats.playerid = players.playerid),
I am trying to do this as part of a block of SET operations for this update.
By the way these are scaled down examples of the tables, I cannot provide exact data unfortunately.
Can anyone help?
-
How about this?
update p
set totalapps = b.apps
, totalgials = b.goals
from player p
inner join (
select a.playerid, sum( a.apps) 'apps', sum( a.goals) 'goals'
from playerstats a
inner join players p1
on a.playerid = p1.playerid
group by a.playerid) b
on p.playerid = b.playerid
-
Just one additional question, I am updating a number of other columns at the same time and I am looking at some additional conditional processing, i.e. I only need to update the column if a specific value is set. A snippet of my SQL is below showing what I am trying to do, I cannot get your solution to work in this scenario, keeps giving me a syntax error?
update p
set totalapps =
CASE @updatecareerapps WHEN 1 THEN b.apps
from @players p
inner join (select a.playerid, sum( a.apps) 'apps'
from playerseason a
inner join @players p1 on a.playerid = p1.playerid
group by a.playerid) b
on p.playerid = b.playerid
ELSE NULL
END
Is this even possible or am I trying to be too clever here?
-
I don't understant very well what you are trying to accomplish but I think you may want something like this:
update p
set
totalapps = Case when @updatecareerapps = 1 then b.apps else Null end
, totalgoals = Case when @updatecareergoals = 1 then b.goals else Null end
from player p
inner join (
select a.playerid, sum( a.apps) [apps], sum( a.goals) [goals]
from playerstats a
inner join players p1
on a.playerid = p1.playerid
group by a.playerid) b
on p.playerid = b.playerid
If what you need is to leave the column value unchanged you may try:
totalapps = Case when @updatecareerapps = 1 then b.apps else totalapps end
, totalgoals = Case when @updatecareergoals = 1 then b.goals else totalgoals end
HTH
* Noel -
Thanks for your help guy's, got it sorted.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply