MySQL error 1241: Operand should contain 1 column(s)

MysqlSqlSelectInsertMysql Error-1241

Mysql Problem Overview


I am trying to Insert data from a table1 into table2

insert into table2(Name,Subject,student_id,result)
select (Name,Subject,student_id,result)
from table1;

Key for table2 is student_id.

Assume that there are not any duplicates.

I get the error: MySQL error 1241: Operand should contain 1 column(s)

There are only four columns in table2.

Mysql Solutions


Solution 1 - Mysql

Syntax error, remove the ( ) from select.

insert into table2 (name, subject, student_id, result)
select name, subject, student_id, result
from table1;

Solution 2 - Mysql

Just remove the ( and the ) on your SELECT statement:

insert into table2 (Name, Subject, student_id, result)
select Name, Subject, student_id, result
from table1;

Solution 3 - Mysql

Another way to make the parser raise the same exception is the following incorrect clause.

SELECT r.name
FROM roles r
WHERE id IN ( SELECT role_id ,
                     system_user_id
                 FROM role_members m
                 WHERE r.id = m.role_id
                 AND m.system_user_id = intIdSystemUser
             )

The nested SELECT statement in the IN clause returns two columns, which the parser sees as operands, which is technically correct, since the id column matches values from but one column (role_id) in the result returned by the nested select statement, which is expected to return a list.

For sake of completeness, the correct syntax is as follows.

SELECT r.name
FROM roles r
WHERE id IN ( SELECT role_id
                 FROM role_members m
                 WHERE r.id = m.role_id
                 AND m.system_user_id = intIdSystemUser
             )

The stored procedure of which this query is a portion not only parsed, but returned the expected result.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionKumaran SenapathyView Question on Stackoverflow
Solution 1 - MysqlDavidView Answer on Stackoverflow
Solution 2 - MysqlfthiellaView Answer on Stackoverflow
Solution 3 - MysqlDavid A. GrayView Answer on Stackoverflow