error : 1242 – Subquery returns more than 1 row

I am trying to calculate the total credits for each studentID in the enrollment table. I have some repeated studentIDs which means that the same student is already enrolled in many sections and each section has different credit. When I tried these queries

    UPDATE
    enrollment
SET
    enrollment.total_balance =(
    SELECT
        SUM(course.Credits * 100)
    FROM
        enrollment
    INNER JOIN section ON enrollment.sectionID = section.ID
    INNER JOIN course ON section.courseID = course.ID
)

It made all studentIDs have the same sum of credits as shown in the picture. Same credit value for each studentIDs

Then I tried to group studentIDs to separate each credit using “GROUP BY”.

    UPDATE
    enrollment
SET
    enrollment.total_balance =(
    SELECT
        SUM(course.Credits * 100)
    FROM
        enrollment
    INNER JOIN section ON enrollment.sectionID = section.ID
    INNER JOIN course ON section.courseID = course.ID
    GROUP BY studentID
)

But I got this message error: #1242 – Subquery returns more than 1 row.

I have tried many things but it didn’t work, I am recently using SQL DB and I just want to make the table as shown in this picture. Final table results

Thanks in advance.

Answer

Your approach using a subquery is fine. It just needs to correlated to the outer query:

UPDATE enrollment e
    SET total_balance = (SELECT SUM(c.Credits * 100)
                         FROM enrollment e2 JOIN
                              section s
                              ON e2.sectionID = s.ID JOIN
                              course c
                              ON s.courseID = c.ID
                         WHERE e2.StudentId = e.StudentId
                        );

Note that table aliases make the query easier to write and to read.

Next . . . something seems wrong with your data model. I don’t see why a table called enrollment should contain the total credits for each (repeated) student. If this information is stored, it should be at the student level.

More importantly, though, you probably shouldn’t be storing this data, because you can readily calculate — and then the data is always accurate.