Allocating students to courses

[sdasilva]

Hi there,

I have two tables: STUDENT and COURSES.

 

TABLE:STUDENT

Student_Number

FirstName

LastName

CourseName

Course_ID

 

TABLE:COURSES

CourseName

Course_ID

CourseTotalCapacity

CourseCurrentNumberofStudents

 

I want to allocate students to courses,so when I add a student to the student table and allocate him a course,it should also populate the TABLE:COURSES.Meaning each time I add a student to a course the attribute CourseCurrentNumberofStudents on the TABLE:COURSES should increment,but I do not know how to add data to one table and make it populate or update the other table.

Any ideas?

[Illusion]

we can use two insert statements sequentially

 

Insert into STUDENT(col1, col2, col3 etc) values (v1, v2, v3 etc); 

 

Assuming that , you have Primary key on course_id

 

INSERT INTO COURSES (COURSE_ID) VALUES(10)  ON duplicate KEY UPDATE CourseCurrentNumberofStudents=CourseCurrentNumberofStudents+1;