I have the following three tables as follows,
select * from student_grade;
+--------------+----------+----------+-------+--------------------------+
| enrollmentid | courseid | personid | grade | credits_earned |
+==============+==========+==========+=======+==========================+
| 1 | 1001 | 1 | A | null |
| 2 | 1002 | 1 | B | null |
| 3 | 1003 | 1 | A | null |
| 4 | 3001 | 3 | A | null |
| 5 | 3001 | 2 | B | null |
| 6 | 4001 | 4 | A | null |
| 7 | 4002 | 4 | A | null |
+--------------+----------+----------+-------+--------------------------+
7 tuples
sql>select * from course;
+----------+-------------------+---------+--------------+
| courseid | title | credits | departmentid |
+==========+===================+=========+==============+
| 1001 | Data structures | 12 | 101 |
| 1002 | Algorithms | 12 | 101 |
| 1003 | Graphics | 20 | 101 |
| 2001 | DSP | 20 | 102 |
| 2002 | Matlab | 20 | 102 |
| 2003 | Maths | 10 | 102 |
| 3001 | CAD | 10 | 104 |
| 4001 | Power electronics | 10 | 103 |
| 4002 | Semi conductors | 20 | 103 |
+----------+-------------------+---------+--------------+
9 tuples
sql>select * from grade_to_credits;
+-----------+--------------------------+
| gradechar | credits |
+===========+==========================+
| A | 1 |
| B | 0.9 |
+-----------+--------------------------+
What I am trying to do is:
I am updating the credits_earned column of student_grade table using the columns of credits of course table and credits of grade_to_credits table.
just like this,
select c.credits * gc.credits
from course c, grade_to_credits gc, student_grade sg
where sg.courseid = c.courseid and sg.grade = gc.gradechar;
+--------------------------+
| L2 |
+==========================+
| 12 |
| 10.8 |
| 20 |
| 10 |
| 9 |
| 10 |
| 20 |
+--------------------------+
I am getting the above values when I execute separately, but now I want to update these values in student_grade table using update query.
Now I am using the query as:
update student_grade
set credits_earned = (select c.credits * gc.credits
from course c, grade_to_credits gc, student_grade sg
where sg.courseid = c.courseid
and sg.grade = gc.gradechar);
But the above query is not working, I am getting error as:
Cardinality violation, scalar value expected
I know, I can individually set the values, but I wanna do it from update command. Please correct me where I am going wrong.
No need to overcomplicate things use LEFT JOIN to achive the result. Set operation are usually more efficient than row by row operation.
update student_grade
SET credits_earned = a.credits_earned
from (
select s.enrollmentid,
s.courseid,
s.personid,
s.grade,
(g.credits * c.credits) as credits_earned
from student_grade s
LEFT OUTER JOIN @grade_to_credits g ON g.gradechar = s.grade
LEFT OUTER JOIN @course c ON c.courseid = s.courseid
) as a
where student_grade.enrollmentid = a.enrollmentid
Result:
1 1001 1 A 12
2 1002 1 B 10,8
3 1003 1 A 20
4 3001 3 A 10
5 3001 2 B 9
6 4001 4 A 10
7 4002 4 A 20