Entering column values into one table from another related table
I have two tables, Students
and Course_Registrations
where each student registers several courses.
In my table Students
I have his degree (MS / BS), but it is not in the table Course_Registrations
. I have tried the below code to get the student curriculum in Course_Registrations
, but I am getting this error:
Scalar subquery is only allowed to return one row
Any suggestions?
Update ROOT.ISB_COURSE_REGISTRATIONS
set ROOT.ISB_COURSE_REGISTRATIONS.degree=
(
Select ROOT.ISB_STUDENTS.degree
from ROOT.ISB_STUDENTS
where ROOT.ISB_STUDENTS.STUDENT_ID=ROOT.ISB_COURSE_REGISTRATIONS.STUDENT_ID
)
where exists
(
select *
from ROOT.ISB_STUDENTS
where ROOT.ISB_STUDENTS.STUDENT_ID=ROOT.ISB_COURSE_REGISTRATIONS.STUDENT_ID
GROUP BY STUDENT_ID
);
source to share
You have a problem because some students have multiple lines in ISB_STUDENTS
. You need to figure out what to do in order to combine them. In the meantime, I would suggest the following:
Update ROOT.ISB_COURSE_REGISTRATIONS
set degree = (Select MAX(s.degree)
from ROOT.ISB_STUDENTS s
where ISB_STUDENTS.STUDENT_ID = s.STUDENT_ID
)
where exists (select 1
from ROOT.ISB_STUDENTS s
where s.STUDENT_ID = ISB_COURSE_REGISTRATIONS.STUDENT_ID
group by s.STUDENT_ID
having min(s.degree) = max(s.degree)
);
Note the use of a clause having
in a subquery in a clause where
. This will only set the value when all levels in the student records are the same. Then you can investigate two problems:
- In the table named
ISB_STUDENTS
why is there a duplicateSTUDENT_ID
s? - When there are duplicates like this, how would you handle multiple inconsistent degrees?
source to share
You can try this:
Update ROOT.ISB_COURSE_REGISTRATIONS
set ROOT.ISB_COURSE_REGISTRATIONS.degree=
(
Select MAX(ROOT.ISB_STUDENTS.degree)
from ROOT.ISB_STUDENTS
where ROOT.ISB_STUDENTS.STUDENT_ID=ROOT.ISB_COURSE_REGISTRATIONS.STUDENT_ID
)
where exists
(
select 1
from ROOT.ISB_STUDENTS
where ROOT.ISB_STUDENTS.STUDENT_ID=ROOT.ISB_COURSE_REGISTRATIONS.STUDENT_ID
);
This correlated subquery is computed for every row in ISB_COURSE_REGISTRATIONS
Select ROOT.ISB_STUDENTS.degree
from ROOT.ISB_STUDENTS
where ROOT.ISB_STUDENTS.STUDENT_ID=ROOT.ISB_COURSE_REGISTRATIONS.STUDENT_ID
So, if it returns multiple rows for a specific one ISB_COURSE_REGISTRATIONS.degree
, it cannot determine which one should be set as the value.
Applying MAX(ROOT.ISB_STUDENTS.degree)
this subquery to all rows ensures that there is only one row.
But you must be careful with this update. Perhaps there are different courses in which students study, and I have different degrees for each course.
source to share
as I understood from your question, you have the following
- student table containing the degree field
- course table that also contains a degree field and you want to update it from the student table for each student.
if so you can try this
Update r set r.degree=s.degree
from ROOT.ISB_COURSE_REGISTRATIONS r
inner join ROOT.ISB_STUDENTS s on r.STUDENT_ID=s.STUDENT_ID
if I misunderstood any of your requirements, please clarify.
hope this helps you
source to share