Insert Statement with multiple select statement

Question

insert into students_learn_subjects(student_id,subject_id) 
select max(id) from students, subject_id from classes_have_subjects where class_id = 5)

I want to insert values in table but retrieve these values from other table. With this query i am getting this error. The second select statement return multiple values but the first one only one

ERROR: syntax error at or near "from"

LINE 2: select max(id) from students, subject_id from classes_have_s...

You specified two fields in insert and one field in the select — armagedescu
Why are you choosing not to use proper, explicit, standard, readable JOIN syntax? — Gordon Linoff

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2020-07-26T05:23:53

I think this is what you want:

INSERT INTO students_learn_subjects (student_id, subject_id) 
SELECT
    (SELECT MAX(id) FROM students),
    subject_id
FROM classes_have_subjects
WHERE class_id = 5;

This assumes that the query against the classes_have_subjects table would return more than one record. The max query against students must, by definition, always return a single scalar value, so it can sit in separate subquery.

Insert Statement with multiple select statement

1 Answers