t-sql 2012 issue with an update statement

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  July 7, 2019 at 4:07 am

  #3660481

  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:

  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?

  • This topic was modified 6 years ago by Steve Jones - SSC Editor. Reason: Formatted code

• DesNorton

  SSC-Insane

  Points: 24540

  More actions

  July 7, 2019 at 6:48 am

  #3660506

  You need to use a CTE or a sub-query to generate your aggregates, and then join to that for the update

  WITH cteUpdate as (
    select Milestone.SCHOOLNUM, Milestone.STULINK, Milestone.SCHOOLYEAR
    , sum(Details.[TOT_ABSENCES]) as TOT_ABSENCES
    from Milestone Milestone
    inner 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
  )
  UPDATE ms
  SET TOT_ABSENCES = upd.TOT_ABSENCES
  FROM Milestone AS ms
  INNER JOIN cteUpdate AS upd
     ON ms.SCHOOLNUM  = cte.SCHOOLNUM
    AND ms.STULINK    = cte.STULINK
    AND ms.SCHOOLYEAR = cte.SCHOOLYEAR;
  

  ---

  How to post data/code on a forum to get the best help.

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  July 7, 2019 at 7:54 pm

  #3660600

  that answers my question