Error code 1241 sql

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 ...

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

Kumaran Senapathy's user avatar

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

David's user avatar

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

fthiella's user avatar

fthiellafthiella

47.5k15 gold badges92 silver badges104 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's user avatar

Tomm

1,0212 gold badges17 silver badges34 bronze badges

answered Mar 5, 2018 at 5:46

David A. Gray's user avatar

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.

Источник

Содержание

  1. How to fix MySQL operand should contain 1 column(s) error
  2. How to fix Operand should contain 1 column(s) error
  3. Level up your programming skills
  4. About
  5. #1241 Operand should contain 1 column(s) in Insert into Select
  6. 3 Answers 3
  7. Related
  8. Hot Network Questions
  9. Subscribe to RSS
  10. MySQL error 1241: Operand should contain 1 column(s)
  11. 3 Answers 3
  12. Linked
  13. Related
  14. Hot Network Questions
  15. Subscribe to RSS

How to fix MySQL operand should contain 1 column(s) error

Posted on Oct 06, 2021

Learn how to fix MySQL error operand should contain 1 column(s)

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:

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:

While the pets table contain the owner and the species column as follows:

The first_name and the owner columns are related, so you may use a subquery to display data from both tables like this:

However, the above SQL query is wrong, and it will throw an error like this:

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:

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:

Now the subqueries will return two species and age rows for “Jessie”, causing another related error:

To properly fix the error, you need to replace the subquery with a JOIN clause:

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:

Without using a subquery, you need to JOIN the table as shown below:

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.

Level up your programming skills

I’m sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I’ll send new stuff straight into your inbox!

About

Nathan Sebhastian is a software engineer with a passion for writing tech tutorials.
Learn JavaScript and other web development technology concepts through easy-to-understand explanations written in plain English.

Источник

#1241 Operand should contain 1 column(s) in Insert into Select

hey Guys i m working on SQL and i got this error

first i ll show you my code

i got the error #1241 — Operand should contain 1 column(s); and if i use

it work perfectly so where’s the problem i want to insert many columns not only one

3 Answers 3

I think you are getting this error as the rows returned by the SELECT is more than one. So you can use a INSERT INTO TABLE SELECT like this:

You should use insert . . . select , and you can use it with a constant:

  • If you want only one row, then add limit 1 .
  • Even when using values, you need single quotes around the date. 2015-03-28 evaluates to 1984 (think «subtraction»). This then gets treated as a date.

The Error You are Getting because Your Sub-Query SELECT CodeEquipement,nomenclature FROM equipement WHERE Numero_Serie =241 Returning More than 1 result.

if you want to insert a single row to the table then you can try:

And if you want to insert all the record from equipement table to HistoriqueEquipement table.

Hot Network Questions

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.1.14.43159

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Источник

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

I am trying to Insert data from a table1 into table2

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.

3 Answers 3

Syntax error, remove the ( ) from select .

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

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

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.

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

Linked

Hot Network Questions

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.1.14.43159

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Источник

Источник

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.

Источник

Answer by Vienna Flores

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.,Another way to make the parser raise the same exception is the following incorrect clause.,The stored procedure of which this query is a portion not only parsed, but returned the expected result.,Find centralized, trusted content and collaborate around the technologies you use most.

Syntax error, remove the ( ) from select.

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

Answer by Whitney Mullen

Incorrect number of columns from subquery:
ERROR 1241 (ER_OPERAND_COL)
SQLSTATE = 21000
Message = «Operand should contain 1 column(s)»
This error occurs in cases like this:
SELECT (SELECT column1, column2 FROM t2) FROM t1;
You may use a subquery that returns multiple columns, if the
purpose is row comparison. In other contexts, the subquery
must be a scalar operand. See
Section 13.2.10.5, “Row Subqueries”.
,
Incorrect number of columns from subquery:
,
Incorrect number of rows from subquery:
,
Unsupported subquery syntax:
ERROR 1235 (ER_NOT_SUPPORTED_YET)
SQLSTATE = 42000
Message = «This version of MySQL doesn’t yet support
‘LIMIT & IN/ALL/ANY/SOME subquery'»
This means that MySQL does not support statements of the
following form:
SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)

Unsupported subquery syntax: 

ERROR 1235 (ER_NOT_SUPPORTED_YET)
SQLSTATE = 42000
Message = "This version of MySQL doesn't yet support
'LIMIT & IN/ALL/ANY/SOME subquery'"

This means that MySQL does not support statements of the
following form: 

SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)

Answer by Benjamin Boone

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. ,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:,Either you can unpivot the data in the team table so you are only returning one column:,Or you would have to use multiple WHERE filters on the player name:

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
);

Answer by Paityn McCormick

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?,Get answers to millions of questions and give back by sharing your knowledge with others.,I ran into the same error when using Spring Repositories.,I don’t have a MySQL instance handy, but my first guess is the WHERE clause:

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

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)

Answer by Estella Todd

If I just use the query in the parentheses it works and the result is,The error code I receive is,I have one more suggestion, are you sure that your inner query will always return one row?
If you want EMAIL to be set with value 0 for multiple IDs returned by inner query I would recommend you use «IN» instead of «=».,Your subquery contains two columns. Try this:

My SQL query:

UPDATE ADRESSEN
SET EMAIL = 0
WHERE ID = (SELECT ID, COUNT(ID) AS COUNTER
FROM EIGENSCHAFTEN WHERE Kategorie = "BOUNCE" 
GROUP BY ID
HAVING COUNTER = 1)

The error code I receive is

#1241 - Operand should contain 1 column(s)

If I just use the query in the parentheses it works and the result is

ID | COUNTER
0002159 | 1
Источник

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

Syntax error, remove the ( ) from select.

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

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;

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

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.

Related posts on MySQL Error :

  • MySQL error code: 1175 during UPDATE in MySQL Workbench
  • MySQL Error Operand should contain 1 column
  • MySQL ERROR 1290 (HY000) –secure-file-priv option
  • mysql error 1364 Field doesnt have a default values
  • sql – MySQL Error 1264: out of range value for column
  • sql – MySQL error: key specification without a key length
  • mySQL Error 1040: Too Many Connection
  • php – mysql error TYPE=MyISAM
Источник

fezarmon

Fezarmon

@fezarmon

Начинающий программист

вот сам запрос

SELECT * FROM `board` WHERE moder=0 AND activ=0 AND id_akk IN (SELECT user_id_to, user_id_from FROM `podpiski` WHERE user_id_from='38')

Я понимаю, что в операторе IN, а точней в SELECT нельзя выбрать два столбца. Могли бы вы написать альтернативный такой же запрос или исправить этот.


  • Вопрос задан

    более года назад

  • 954 просмотра


Комментировать


Решения вопроса 1

fezarmon

Fezarmon

@fezarmon Автор вопроса

Начинающий программист 

SELECT * 
FROM `board` 
WHERE moder=0 AND activ=0 AND id_akk IN (SELECT user_id_to
                                         FROM `podpiski` 
                                         WHERE user_id_from='38'
                                         union
                                         SELECT user_id_from
                                         FROM `podpiski` 
                                         WHERE user_id_from='38')


Комментировать

Пригласить эксперта


Похожие вопросы

  • Показать ещё
    Загружается…

09 февр. 2023, в 15:13

2000 руб./за проект

09 февр. 2023, в 15:06

2000 руб./за проект

09 февр. 2023, в 15:02

12000 руб./за проект

Минуточку внимания

Источник

Понравилась статья? Поделить с друзьями:
  • Error code 1222 the used select statements have a different number of columns
  • Error code 1222 mysql
  • Error code 12150
  • Error code 1215 cannot add foreign key constraint mysql
  • Error code 121003