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
48k15 gold badges89 silver badges105 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 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)
You cannot have multiple columns being returned in a subquery like that, so you have several ways that you would have rewrite this query to work.
Either you can unpivot the data in the team table so you are only returning one column:
select *
from players
where sport='football'
and position='DEF'
and pname!='Binoy Dalal'
and pname not in (select player1
from team where sap='60003100009'
union all
select player2
from team where sap='60003100009'
union all
select player3
from team where sap='60003100009'
union all
select player4
from team where sap='60003100009'
union all
select player5
from team where sap='60003100009'
union all
select player6
from team where sap='60003100009'
union all
select player7
from team where sap='60003100009'
union all
select player8
from team where sap='60003100009')
order by price desc;
Or you can use a NOT EXISTS query:
select *
from players p
where sport='football'
and position='DEF'
and pname!='Binoy Dalal'
and not exists (select *
from team t
where sap='60003100009'
AND
(
p.pname = t.player1 OR
p.pname = t.player2 OR
p.pname = t.player3 OR
p.pname = t.player4 OR
p.pname = t.player5 OR
p.pname = t.player6 OR
p.pname = t.player7 OR
p.pname = t.player8
))
order by price desc;
Or you would have to use multiple WHERE filters on the player name:
select *
from players
where sport='football'
and position='DEF'
and pname!='Binoy Dalal'
and pname not in (select player1
from team where sap='60003100009')
and pname not in (select player2
from team where sap='60003100009')
and pname not in (select player3
from team where sap='60003100009')
and pname not in (select player4
from team where sap='60003100009')
and pname not in (select player5
from team where sap='60003100009')
and pname not in (select player6
from team where sap='60003100009')
and pname not in (select player7
from team where sap='60003100009')
and pname not in (select player8
from team where sap='60003100009')
order by price desc;
However, ideally you should consider normalizing the team table so you have one column with the player name and another column that assigns them a player number. Similar to this:
create table team
(
player varchar(50),
playerNumber int
);
Then when you are searching the team data you only have to join on one column instead of 8 different columns.
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.
Вопрос:
INSERT INTO People(Track_id_Reference)
SELECT track_id
FROM Tracks
WHERE track_title IN (SELECT tracktitle
FROM top100
WHERE artist IN (SELECT p.People_name, t.artist
FROM People AS p
RIGHT JOIN top100 AS t
ON
p.People_name=t.artist
UNION DISTINCT
SELECT p.People_name, t.artist
FROM People AS p
LEFT JOIN top100 AS t
ON
p.People_name=t.artist));Ошибка, которую я получаю,
ERROR 1241 (21000): Operand should contain 1 column(s)
подзапрос, объединения которого возвращают 2 столбца. Как я могу это исправить?
Лучший ответ:
вам не хватает FROM предложение
SELECT track_id
FROM tableName
WHERE track_title
поэтому полный запрос будет
INSERT INTO People (Track_id_Reference)
SELECT track_id
FROM -- <<== add tableName here
WHERE track_title = (
SELECT tracktitle
FROM top100
WHERE artist = (
SELECT p.People_name,
t.artist
FROM People AS p
RIGHT JOIN top100 AS t
ON p.People_name = t.artist
UNION
DISTINCT
SELECT p.People_name,
t.artist
FROM People AS p
LEFT JOIN top100 AS t
ON p.People_name = t.artist
)
);
еще одна проблема, которая возникнет в ближайшее время, – это использование знака = в результате подзапроса, гораздо безопаснее использовать IN, чем =, потому что он будет генерировать исключение, если, например, в подзапросе будет возвращено более одного значения. Пример,
INSERT INTO People (Track_id_Reference)
SELECT track_id
FROM -- <<== add tableName here
WHERE track_title IN (
SELECT tracktitle
FROM top100 .............