INSERT query with subquery: the number of columns does not match the number of values in row 1
I have this query that inserts rows using a subquery like this:
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 understand, the sub select query returns 4 columns, the same number as the INSERT query. Why is this giving me the error:
Column count doesn't match value count at row 1
Any ideas?
+3
Marten sytema
source
to share
3 answers
instead INSERT INTO VALUES
, useINSERT 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 there will be more than one record in your request will work INSERT
.
+4
Taryn
source
to share
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
and what's the empty value for presence_id
?
+1
triclosan
source
to share
INSERT INTO Lecture_presence_Student (`presence_id`, `Lecture_id`, `Student_id`, `status`)
SELECT '', 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
Remove "VALUES (())" and no alias is needed.
+1
Francis P
source
to share