Post reply

t-sql 2012 issue with an update statement

  • 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?

  • 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.

  • July 7, 2019 at 7:54 pm

    #3660600

    that answers my question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply