Updating column based on other two tables

Question

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.

your subquery to get credits_earned must return only one row. I believe it is returning multiple rows. — Madhur Bhaiya, Sep 08 '18 at 07:01
yes, it is returning multiple rows, Cant we iterate for each row while setting the value? — Anil, Sep 08 '18 at 07:03
check my answer. https://stackoverflow.com/a/52232879/2469308 If it works for you, please upvote and accept it as well :) — Madhur Bhaiya, Sep 08 '18 at 07:12

SNR · Accepted Answer · 2018-09-08T07:43:35.053

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

But it is not updating the table – Anil Sep 08 '18 at 07:37 — Anil, Sep 08 '18 at 07:37

Madhur Bhaiya · Answer 2 · 2018-09-08T07:56:19.020

1

NOTE: The solution below works in MySQL. (OP later retagged/changed question from MySQL to MonetDB)

Problem with your attempt is that the inner subquery is returning more than one rows.
Even if it returns only one row, another issue with it would be updating all the credits_earned field in the table student_grade with the same value (output of the inner subquery).
You should avoid using Implicit joins. They are old and not a good practice.

You should rather INNER JOIN these tables, and then use the joined tables' columns to update rowwise data. Try the following:

update student_grade sg 
inner join course  c on c.courseid = sg.courseid 
inner join grade_to_credits gc on gc.gradechar = sg.grade 
set sg.credits_earned = c.credits * gc.credits;

edited Sep 08 '18 at 07:56

answered Sep 08 '18 at 07:06

Madhur Bhaiya

28,155
10
49
57

getting the error as : syntax error, unexpected IDENT, expecting SET in: "update student_grade sg" – Anil Sep 08 '18 at 07:35
@Anil typo was there. i have fixed it please check again. hope you rectify your downvote also. it was a mere typo! – Madhur Bhaiya Sep 08 '18 at 07:37
still getting the same error, can you please tell me the typo? – Anil Sep 08 '18 at 07:38
@Anil there was `on` twice in the second inner join. i just removed the one of the `on` – Madhur Bhaiya Sep 08 '18 at 07:39
UPDATE student_grade sg inner join course c on c.courseid = sg.courseid inner join grade_to_credits gc on gc.gradechar = sg.grade set sg.credits_earned = c.credits * gc.credits; – Anil Sep 08 '18 at 07:40
it is giving the error for the above query as well, where it is not having on on . – Anil Sep 08 '18 at 07:41
@Anil what is the error ? please give the complete error message here. – Madhur Bhaiya Sep 08 '18 at 07:41
UPDATE student_grade sg inner join course c on c.courseid = sg.courseid inner join grade_to_credits gc on gc.gradechar = sg.grade set sg.credits_earned = c.credits * gc.credits; syntax error, unexpected IDENT, expecting SET in: "update student_grade sg" – Anil Sep 08 '18 at 07:42
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/179670/discussion-between-anil-and-madhur-bhaiya). – Anil Sep 08 '18 at 07:42

score 0 · Answer 3 · answered Sep 08 '18 at 07:15

If I understand correctly, you just need to remove student_grade from the subquery:

update student_grade st
    set credits_earned = (select sc.credits * gc.credits
                          from course c cross join
                               grade_to_credits gc
                          where sg.courseid = c.courseid and
                                sg.grade = gc.gradechar
                         );

Updating column based on other two tables

3 Answers3