0

There are 3 tables. I use mysql. students can enroll in many course(many to many)

CREATE TABLE `classes`
(
    `name` VARCHAR(20),
    `class_id` VARCHAR(20)
);

CREATE TABLE `students`
(
    `name` VARCHAR(20),
    `student_id` INT(11)
);

CREATE TABLE `studentsclasses`
(
    `class_id` VARCHAR(20), 
    `student_id` INT(11)
);

Date in table "classes" is populated before students table is. Is it possible to have one single insert instruction that can insert data into "student" table and "studentsclasses" at the same time? Google suggest that the work around is to have two insert operations at application front end, but is there a better way to do this(like trigger) which requiring less programming on front end application?

Edit: more specific question

King Li
  • 1
  • 2
  • How would MySQL know with which classes a newly inserted student should be associated? You can implement logic of this sort using a [trigger](http://dev.mysql.com/doc/en/triggers.html), or else by performing the insertion within a stored procedure, but ultimately DML operations always operate on one distinct table. – eggyal Aug 21 '13 at 10:37
  • Here is a simular question on stack overflow: http://stackoverflow.com/questions/175066/sql-server-is-it-possible-to-insert-into-two-tables-at-the-same-time – hogni89 Aug 21 '13 at 10:38
  • @hogni89: That question (and its answers) are specific to SQL Server. This question is tagged MySQL. – eggyal Aug 21 '13 at 10:39
  • @eggyal can this be achieved with one insert statement? You just mention that mysql won't be able to tell which to add – King Li Aug 21 '13 at 11:09
  • That's funny. I thought I mentioned triggers and stored procedures too. – eggyal Aug 21 '13 at 11:22
  • @eggyal how can I specify both student information and the class to be enrolled? From my understanding, triggers are added to insert or update statement, if so,which table should the trigger be added to and what statement should I execute? Mysql would not allowed me to insert more fields than there is in a table. – King Li Aug 21 '13 at 11:57

0 Answers0