Why Does My MySQL INSERT Statement Get the 'Operand should contain 1 column(s)' Error?

Understanding MySQL's "Operand should contain 1 column(s)" Error

MySQL's error message "Operand should contain 1 column(s)" indicates a syntax error related to the number of columns returned by a subquery used within an INSERT statement's SELECT clause.

In this specific instance, when attempting to insert records into the VOUCHER table using a SELECT clause that joins two tables (temp_cheques and BOOK), the user encountered this error. The query was attempting to insert multiple columns (number, ID, and DENOMINATION) from the SELECT clause into a single column (VOUCHER_NUMBER).

Correcting the Syntax

To resolve the error, the user needs to ensure that the SELECT clause returns only one column, which will become the value of the target column in the INSERT statement. One way to achieve this is to remove the parentheses from the SELECT clause, as per the correct syntax highlighted in the answer provided:

INSERT INTO VOUCHER (VOUCHER_NUMBER)
SELECT a.number
FROM temp_cheques a, BOOK b
WHERE a.number BETWEEN b.START_NUMBER AND b.START_NUMBER+b.UNITS-1;

Additional Considerations

In addition to correcting the syntax, it's important to note the warning regarding the cross-reference operation resulting in a potentially large query if both BOOK and temp_cheques contain many rows. To allow the query to execute, it may be necessary to set SQL_BIG_SELECTS = 1 before running it, as mentioned in the answer.

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