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

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:

INSERT INTO DestinationTable (DestinationColumns)
SELECT SelectColumns
FROM SourceTable
WHERE Conditions;

In the provided query:

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括号in the SELECT clause are incorrect. Remove them, yielding:

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;

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:

SET SQL_BIG_SELECTS = 1;

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:

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;

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!