Select and Insert in SQL

Question

Table: students

studentid | rollNo | name

Table: classRecord

recordId | studentId | isPresent

students.studentid is the foreign key for classRecord.studentId

I have the value of the rollNo from the students table and also whether he was present in class or not and I want to get the studentid from the students table and insert it into classRecord. I want to first get the value of studentid for a rollNo and then Insert this value of studentid into classRecord table.

You can use the PHP Method to get an associative array? I'm a bit confused as to what your question title has to do with the body — MiltoxBeyond
– MiltoxBeyond, Commented Feb 17, 2017 at 14:43
Sorry. Forgot to change the title. It is based only on pure sql — Ayush Bansal
– Ayush Bansal, Commented Feb 17, 2017 at 14:48

tjcertified · Accepted Answer · 2017-02-17 16:24:09Z

3

You need to do a subquery to select the student ID from the student table, and insert it into the classRecord table:

INSERT INTO classRecord (studentId, isPresent)
    SELECT student.studentId, <is_present_value> 
    FROM student 
    WHERE student.rollNo = <rollNo_value>;

edited Feb 17, 2017 at 16:24

answered Feb 17, 2017 at 15:03

tjcertified

7147 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tjcertified Over a year ago

Added some explanatory text.

Jonathan Leffler Over a year ago

Do you need to insert three column values into a table with three columns? Or specify that you're skipping the recordID column by listing the other two: INSERT INTO classRecord(studentId, isPresent) SELECT …?

tjcertified Over a year ago

Oops. You are correct. I have modified this answer to reflect this.

Collectives™ on Stack Overflow

Select and Insert in SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related