INSERT query with subquery: Column count doesn't match value count at row 1

Question

I have this query that inserts rows, using a subquery like so:

INSERT INTO Lecture_presence_Student (`presence_id`, `Lecture_id`, `Student_id`, `status`) VALUES

(
 (
  SELECT '' as presence_id, Lecture.Lecture_id, CourseEdition_students_Student.Student_id, 'onverwerkt' 
  FROM
    CourseEdition_students_Student 
        INNER JOIN Lecture ON    CourseEdition_students_Student.CourseEdition_id = Lecture.CourseEdition_id



    )       
)

I don't get it, the sub select query returns 4 columns, the same number as the INSERT query. Why does it give me the error:

Column count doesn't match value count at row 1

Any ideas?

Answer 1

instead of using INSERT INTO VALUES, use INSERT INTO SELECT FROM

INSERT INTO Lecture_presence_Student 
(
    `presence_id`
    , `Lecture_id`
    , `Student_id`
    , `status`
) 
SELECT '' as presence_id
    , Lecture.Lecture_id
    , CourseEdition_students_Student.Student_id
    , 'onverwerkt' 
FROM    CourseEdition_students_Student 
INNER JOIN Lecture 
    ON    CourseEdition_students_Student.CourseEdition_id = Lecture.CourseEdition_id

then if you get more than one record in your query, the INSERT will work.