Home > Database > Mysql Tutorial > Why Does My MySQL INSERT-SELECT Query Return 'Operand should contain 1 column(s)'?

Why Does My MySQL INSERT-SELECT Query Return 'Operand should contain 1 column(s)'?

Susan Sarandon
Release: 2024-12-20 08:43:10
Original
192 people have browsed it

Why Does My MySQL INSERT-SELECT Query Return

MySQL Syntax Error: "Operand should contain 1 column(s)" in an INSERT-SELECT Query

When executing an INSERT-SELECT query, MySQL may throw an error stating "Operand should contain 1 column(s)". To troubleshoot this issue, consider the following:

Syntax

The correct syntax for an INSERT-SELECT query is:

1

2

3

4

INSERT INTO DestinationTable (DestinationColumns)

SELECT SelectColumns

FROM SourceTable

WHERE Conditions;

Copy after login

In the provided query:

1

2

3

4

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;

Copy after login

the括号in the SELECT clause are incorrect. Remove them, yielding:

1

2

3

4

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;

Copy after login

Data Size

After correcting the syntax, if the error persists, consider the size of the data involved in the query. MySQL limits the number of rows that can be returned by a SELECT statement (MAX_JOIN_SIZE). To avoid this error for large data sets, execute:

1

SET SQL_BIG_SELECTS = 1;

Copy after login

before running the query. This will increase the limit and allow the query to execute successfully.

Example

Using the corrected syntax and setting SQL_BIG_SELECTS, the modified query becomes:

1

2

3

4

5

6

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;

Copy after login

Execute this query to insert the data from temp_cheques into the VOUCHER table.

The above is the detailed content of Why Does My MySQL INSERT-SELECT Query Return 'Operand should contain 1 column(s)'?. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template