I cannot copy my data with relationship to another table MySQL

user_14287512 0
Viewed 47

I cannot copy SID to my master table with the relationship matching the copies while student id and academic year are equal in each table. After I ran my code, the SID field in master table was still NULL but I got a return message said that there are rows affected. Anyone knows what is wrong with my code?

Here is my copying function code:

ALTER TABLE coursework1.mastertable 
ADD SID int;

insert into 
coursework1.mastertable(SID)
 SELECT distinct
 student.SID
 FROM
coursework1.student
inner join 
coursework1.mastertable on student.StudentID = mastertable.StudentID AND student.Academic_Year = mastertable.Academic_Year;
mysql sql
1 Answers

After you altered the table SID is set to NULL by default. I assume you want to update the records as you inner join data between student and mastertable tables. This should work for you:

UPDATE coursework1.mastertable(SID) AS m
INNER JOIN coursework1.student AS s ON s.StudentID = m.StudentID AND s.Academic_Year = m.Academic_Year
SET m.SID = s.SID;
user_147020070
Related