How to return multi-rows from an update in a sub-query?

Question

I am getting an error

Subquery returned more than 1 value

I want to use my sub-query to return more than one value to multi-rows. I think Use TOP 1 will not be the solution because the values will be all the same. How should I modify my code in order to return multiple values of my sum function?

Here is my code:

UPDATE statistic 
SET Autumn_Credits = (SELECT SUM (modules.Module_Credits)
                      FROM student
                      INNER JOIN course ON student.CourseID = course.CourseID
                      INNER JOIN department ON course.DeptID = department.DeptID
                      INNER JOIN Modules ON department.DeptID = Modules.deptID
                      INNER JOIN Mark ON Modules.ModuleID = Mark.ModuleID 
                                      AND student.SID = Mark.SID
                      WHERE Modules.Session = 'Autumn UK'
                      GROUP BY Student.StudentID, student.Academic_Year)

Answer 1

Presumably you want a correlated subquery. Assuming you have one row per student/academic year in statistic:

update statistic
    set Autumn_Credits = (Select sum(modules.Module_Credits)
                          . . .
     
                          where statistic.StudentId = Student.StudentID and
                                statistic.Academic_Year = student.Academic_Year
                         )

Do note this updates all rows in statistic. If rows don't match the subquery, they will get NULL values.