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.
asked Apr 4, 2013 at 19:43
Syntax error, remove the ( ) from select.
insert into table2 (name, subject, student_id, result)
select name, subject, student_id, result
from table1;
answered Apr 4, 2013 at 19:45
1
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;
answered Apr 4, 2013 at 19:44
fthiellafthiella
48.1k15 gold badges90 silver badges106 bronze badges
2
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.
Tomm
1,0212 gold badges14 silver badges34 bronze badges
answered Mar 5, 2018 at 5:46
0
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.
asked Apr 4, 2013 at 19:43
Syntax error, remove the ( ) from select.
insert into table2 (name, subject, student_id, result)
select name, subject, student_id, result
from table1;
answered Apr 4, 2013 at 19:45
1
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;
answered Apr 4, 2013 at 19:44
fthiellafthiella
48.1k15 gold badges90 silver badges106 bronze badges
2
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.
Tomm
1,0212 gold badges14 silver badges34 bronze badges
answered Mar 5, 2018 at 5:46
0
The error Operand should contain 1 column(s) is most likely caused by a subquery that’s returning more than one column.
Here’s a typical SELECT query that causes this error:
SELECT column_one,
(SELECT column_two, column_three FROM table_two)
FROM table_one;
The above subquery returns column_two and column_three, so MySQL throws the Operand should contain 1 column(s) error.
Most often, you only need to check your subquery and make sure that it returns only one column.
If you need more guidance on how to fix this MySQL error, then you may read the next section.
How to fix Operand should contain 1 column(s) error
To illustrate an example, imagine you have two tables that have related data named members and pets.
The members table contain the first_name of people who have pets as shown below:
+----+------------+----------------+
| id | first_name | country |
+----+------------+----------------+
| 1 | Jessie | United States |
| 2 | Ann | Canada |
| 3 | Joe | Japan |
| 4 | Mark | United Kingdom |
| 5 | Peter | Canada |
+----+------------+----------------+
While the pets table contain the owner and the species column as follows:
+----+--------+---------+------+
| id | owner | species | age |
+----+--------+---------+------+
| 1 | Jessie | bird | 2 |
| 2 | Ann | duck | 3 |
| 3 | Joe | horse | 4 |
| 4 | Mark | dog | 4 |
| 5 | Peter | dog | 5 |
+----+--------+---------+------+
The first_name and the owner columns are related, so you may use a subquery to display data from both tables like this:
SELECT `first_name` AS `owner_name`,
(SELECT `species`, `age`
FROM pets WHERE pets.owner = members.first_name)
FROM members;
However, the above SQL query is wrong, and it will throw an error like this:
ERROR 1241 (21000): Operand should contain 1 column(s)
This is because MySQL expects the subquery to return only one column, but the above subquery returns two.
To fix the error, you may create two subqueries with each subquery returning only one column as in the following SELECT statement:
SELECT `first_name` AS `owner_name`,
(SELECT `species`
FROM pets WHERE pets.owner = members.first_name) AS `species`,
(SELECT `age`
FROM pets WHERE pets.owner = members.first_name) AS `age`
FROM members;
While the above query works, it will throw another error once the subquery returns more than one row.
Let’s add another pet that’s owned by “Jessie” to the pets table as shown below:
+----+--------+---------+------+
| id | owner | species | age |
+----+--------+---------+------+
| 1 | Jessie | bird | 2 |
| 2 | Ann | duck | 3 |
| 3 | Joe | horse | 4 |
| 4 | Mark | dog | 4 |
| 5 | Peter | dog | 5 |
| 6 | Jessie | cat | 4 |
+----+--------+---------+------+
Now the subqueries will return two species and age rows for “Jessie”, causing another related error:
mysql> SELECT `first_name` AS `owner_name`,
-> (SELECT `species`
-> FROM pets WHERE pets.owner = members.first_name)
-> FROM members;
ERROR 1242 (21000): Subquery returns more than 1 row
To properly fix the error, you need to replace the subquery with a JOIN clause:
SELECT `first_name` AS `owner_name`, `species`, `age`
FROM members JOIN pets
ON members.first_name = pets.owner;
Subqueries can be used to replace JOIN clauses only when you need to SELECT data from one table, but you need to filter the result by another table column.
For example, maybe you have some owner names in the pets table that aren’t recorded in the members table. You can use a subquery in the WHERE clause to display rows in the pets table that are also recorded in the members table.
Here’s an example of using a subquery in the WHERE clause:
SELECT `owner`, `species`, `age`
FROM pets
WHERE `owner` IN (SELECT `first_name` FROM members);
Without using a subquery, you need to JOIN the table as shown below:
SELECT `owner`, `species`, `age`
FROM pets JOIN members
ON pets.owner = members.first_name;
The two queries above will produce the same result set.
And that’s how you can fix the Operand should contain 1 column(s) error in MySQL.
You need to check your subquery before anything else when you encounter this error.
I tried running the following statement:
INSERT INTO VOUCHER (VOUCHER_NUMBER, BOOK_ID, DENOMINATION)
SELECT (a.number, b.ID, b.DENOMINATION)
FROM temp_cheques a, BOOK b
WHERE a.number BETWEEN b.START_NUMBER AND b.START_NUMBER+b.UNITS-1;
which, as I understand it, should insert into VOUCHER each record from temp_cheques with the ID and DENOMINATION fields corresponding to entries in the BOOK table (temp_cheques comes from a database backup, which I’m trying to recreate in a different format). However, when I run it, I get an error:
Error: Operand should contain 1 column(s)
SQLState: 21000
ErrorCode: 1241
I’m running this in SQuirrel and have not had issues with any other queries. Is there something wrong with the syntax of my query?
EDIT:
The structure of BOOK is:
ID int(11)
START_NUMBER int(11)
UNITS int(11)
DENOMINATION double(5,2)
The structure of temp_cheques is:
ID int(11)
number varchar(20)
Bill Karwin
539k86 gold badges674 silver badges830 bronze badges
asked Jan 19, 2009 at 5:50
9
Try removing the parenthesis from the SELECT clause. From Microsoft TechNet, the correct syntax for an INSERT statement using a SELECT clause is the following.
INSERT INTO MyTable (PriKey, Description)
SELECT ForeignKey, Description
FROM SomeView
The error you’re getting, «The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay.», is actually correct, assuming you have many rows in both BOOK and temp_cheques. You are trying to query all rows from both tables and make a cross-reference, resulting in an m*n size query. SQL Server is trying to warn you of this, before performing a potentially long operation.
Set SQL_BIG_SELECTS = 1 before running this statement, and try again. It should work, but note that this operation may take a long time.
answered Jan 19, 2009 at 6:34
lc.lc.
114k20 gold badges158 silver badges187 bronze badges
0
Does B contain the UNITS column?
What is the table structure for temp_cheques and Book?
EDIT: As I said in comments, all the columns should be numeric when doing +/- and when comparing.
Does the following simple SELECT work?
SELECT b.START_NUMBER+b.UNITS-1 FROM Books B
answered Jan 19, 2009 at 5:58
shahkalpeshshahkalpesh
33.2k3 gold badges63 silver badges88 bronze badges
7
I don’t have a MySQL instance handy, but my first guess is the WHERE clause:
WHERE a.number BETWEEN b.START_NUMBER AND b.START_NUMBER+b.UNITS-1;
I imagine that the MySQL parser may be interpreting that as:
WHERE number
(BETWEEN start_number AND start_number) + units - 1
Try wrapping everything in parentheses, ie:
WHERE a.number BETWEEN b.START_NUMBER AND (b.START_NUMBER + b.UNITS - 1);
answered Jan 19, 2009 at 6:11
RJHunterRJHunter
2,8293 gold badges25 silver badges30 bronze badges
3
The final version of the query is as follows:
Set SQL_BIG_SELECTS = 1;
INSERT INTO VOUCHER (VOUCHER_NUMBER, BOOK_ID, DENOMINATION)
SELECT a.number, b.ID, b.DENOMINATION
FROM temp_cheques a, BOOK b
WHERE a.number BETWEEN b.START_NUMBER AND (b.START_NUMBER+b.UNITS-1);
The parsing of the BETWEEN statement required parentheses, the SELECT did not, and because of the size of the two tables (215000 records in temp_cheques, 8000 in BOOK) I was breaking a limit on the select size, requiring me to set SQL_BIG_SELECTS = 1.
answered Jan 19, 2009 at 6:33
ElieElie
13.7k23 gold badges74 silver badges128 bronze badges
2
I ran into the same error when using Spring Repositories.
My repository contained a method like:
List<SomeEntity> findAllBySomeId(List<String> ids);
This is working fine when running integration tests against an in-memory database (h2). However against a stand alone database like MySql is was failing with the same error.
I’ve solved it by changing the method interface to:
List<someEntity findBySomeIdIn(List<String> ids);
Note: there is no difference between find and findAll. As described here: Spring Data JPA difference between findBy / findAllBy
answered Jul 4, 2018 at 7:19
BitfulByteBitfulByte
4,1571 gold badge31 silver badges40 bronze badges
MySQL error 1241 is a common error that occurs when running a query that references multiple columns in a WHERE clause that should only reference one column. This error typically occurs when a subquery returns multiple values or columns.
Causes of the error
The error message “Operand should contain 1 column(s)” indicates that the query is referencing more than one column in the WHERE clause, when it should only reference one. This can occur in a number of different ways, including:
- Using a subquery that returns multiple columns or values.
- Using a subquery that returns more than one row.
- Using a JOIN operation that references more than one column in the WHERE clause.
Example 1: Using a Subquery with Multiple Columns
Suppose we have two tables, “orders” and “order_items”, and we want to find all orders where the total price of the items is greater than $100. We might try to use a subquery to do this:
SELECT order_id FROM orders WHERE (SELECT SUM(price * quantity) FROM order_items WHERE order_id = orders.order_id) > 100;
This query will result in the 1241 error, because the subquery returns multiple columns (the order_id and the total price) instead of just one.
To fix the error, we need to modify the subquery to return only a single value:
SELECT order_id FROM orders WHERE (SELECT SUM(price * quantity) FROM order_items WHERE order_id = orders.order_id) > 100;
Example 2: Using a Subquery with Multiple Rows
Suppose we have two tables, “orders” and “order_items”, and we want to find all orders where the total price of the items is greater than $100. We might try to use a subquery to do this:
SELECT order_id FROM orders WHERE (SELECT price * quantity FROM order_items WHERE order_id = orders.order_id) > 100;
This query will result in the 1241 error, because the subquery returns multiple rows instead of just one.
To fix the error, we need to modify the subquery to return only a single row:
SELECT order_id FROM orders WHERE (SELECT SUM(price * quantity) FROM order_items WHERE order_id = orders.order_id) > 100;
Example 3: Using a JOIN with Multiple Columns
Suppose we have two tables, “orders” and “customers”, and we want to find all orders placed by customers with the last name “Smith”. We might try to use a JOIN to do this:
SELECT order_id FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE customers.last_name = 'Smith';
This query will result in the 1241 error, because the WHERE clause references two columns (orders.customer_id and customers.last_name) instead of just one.
To fix the error, we need to modify the query to reference only one column in the WHERE clause:
SELECT order_id FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE customers.last_name = 'Smith';
Conclusion
The MySQL error 1241 can be frustrating to deal with, but it is usually caused by a simple mistake in the query. By understanding the common causes of this error and how to fix them, you can quickly resolve the issue and get back to working with your database.