mysql insert with multiple select statement

Question

I want to insert data to a table with values coming from 3 different tables

table class_teachers_section
class_ref teachers_ref section_ref
2 3 2 1 6 2

table class id name hours 1 pe 3

table teachers id first_name last_name 1 Lenovo Lenovo

table section id name 1 grade 4

class_teachers_section is a intermediary table ... here is my statement

INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref)
    values (
        (class_ref, (select class.id as class_ref from class where class.name = 'pe')),
        (teachers_ref, (select teachers.id as teachers_ref from teachers where
                    teachers.last_name = 'lenevo')),
        (section_ref, (select section.id as section_ref from section where section.name =
                   'grade 4'))
    )

1241 - Operand should contain 1 column(s)

do you guys have any idea how to resolve this? thanks

tried this ..

INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref)
    values( (select class.id as class_ref from class where class.name = 'pe'),
           (select teachers.id as teachers_ref from teachers where teachers.last_name = 'lenevo'),
           (select section.id as section_ref from section where section.name = 'grade 4'))

teachers_ref can not be null error

You should omit the values clause and use the INSERT ... SELECT syntax. — VMai
– VMai, Commented Aug 7, 2014 at 2:27

Gordon Linoff · Accepted Answer · 2014-08-07 03:02:47Z

2

Your syntax is rather unusual. You have three columns in the insert list but have six elements in the values list. Three of them appear to be column names.

That is not how select works. Instead, the columns correspond by position.

In your case, though, you should use insert . . . select:

INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref)
    select (select class.id as class_ref from class where class.name = 'pe'),
           (select teachers.id as teachers_ref from teachers where teachers.last_name = 'lenevo'),
           (select section.id as section_ref from section where section.name = 'grade 4');

edited Aug 7, 2014 at 3:02

answered Aug 7, 2014 at 2:32

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Gordon Linoff Over a year ago

@RonaldManlapao . . . I can see why it might have missed section_ref -- the parentheses were unbalanced. It will insert NULL if subqueries do not find any rows.

Collectives™ on Stack Overflow

mysql insert with multiple select statement

1241 - Operand should contain 1 column(s)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1241 - Operand should contain 1 column(s)

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related