SQLTeam.com | Weblogs | Forums

T-sql 2012 issue with an update statement

My goal is to change the t-sql 2012 listed below so that it updates the
Milestone.[TOT_ABSENCES] field with the value sum(Details.[TOT_ABSENCES]).

Here is the sql that works correctly on a select statement:

select Milestone.SCHOOLNUM,Milestone.STULINK,Milestone.SCHOOLYEAR
,sum(Details.[TOT_ABSENCES]) as TOT_ABSENCES
from Milestone Milestone
join Details Details
on Details.SCHOOLNUM =Milestone.SCHOOLNUM
and Details.STULINK =Milestone.STULINK
and Details.SCHOOLYEAR =Milestone.SCHOOLYEAR
where Milestone.MILESTONE_CODE= '005'
and Details.schoolyear = (select max(schoolyear) FROM Semester)
group by Milestone.SCHOOLNUM,Milestone.STULINK,Milestone.SCHOOLYEAR

However I get an error message about does not like the group by on the following sql:
update Milestone
set TOT_ABSENCES = sum(Details.[TOT_ABSENCES])
from Milestone Milestone
join Details Details
on Details.SCHOOLNUM =Milestone.SCHOOLNUM
and Details.STULINK =Milestone.STULINK
and Details.SCHOOLYEAR =Milestone.SCHOOLYEAR
where Milestone.MILESTONE_CODE= '005'
and Details.schoolyear = (select max(schoolyear) FROM Semester)
group by Milestone.SCHOOLNUM,Milestone.STULINK,Milestone.SCHOOLYEAR

Thus could you show me and/or give me possible suggestions on how to modify the
sql I am referring to?

i dont think you can use update and group by in that syntax

like this SYNTAX is possible

SQL .. like this syntax
;WITH t2 (column1, column2) 
     AS (SELECT t1.column1, 
                Sum(t2.column2) 
         FROM   table1 t1 
                JOIN table2 t2 
                  ON t1.column1 = t2.column1 
         GROUP  BY t1.column1) 
UPDATE t1 
SET    totalvalue = t2.column2 
FROM   table1 t1 
       INNER JOIN t2 
               ON t2.column1 = t1.column1

I tried to so this

please see if it works

SQL i tried
;WITH t2 
     AS (SELECT milestone.schoolnum, 
                milestone.stulink, 
                milestone.schoolyear, 
                Sum(details.[tot_absences]) AS SUMTOT_ABSENCES 
         FROM   details 
                JOIN milestone 
                  ON details.schoolnum = milestone.schoolnum 
                     AND details.stulink = milestone.stulink 
                     AND details.schoolyear = milestone.schoolyear 
         WHERE  milestone.milestone_code = '005' 
                AND details.schoolyear = (SELECT Max(schoolyear) 
                                          FROM   semester) 
         GROUP  BY milestone.schoolnum, 
                   milestone.stulink, 
                   milestone.schoolyear) 
UPDATE milestone 
SET    tot_absences = sumtot_absences 
FROM   milestone t1 
       INNER JOIN t2 
               ON t2.schoolnum = T1.schoolnum 
                  AND t2.stulink = T1.stulink 
                  AND t2.schoolyear = T1.schoolyear
1 Like