MySQL INSERT ... SELECT Error: Why 'Operand Should Contain 1 Column(s)'?

MySQL: Understanding the "Operand Should Contain 1 Column(s)" Syntax Error

The Query and the Error

When attempting to execute an INSERT statement using a SELECT clause, as shown below, you encounter an error:

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 error message you receive is:

Error: Operand should contain 1 column(s)
SQLState:  21000
ErrorCode: 1241

Syntax Correction

The error indicates a syntax issue with the SELECT clause. To resolve this, remove the parentheses from the SELECT clause and write it as follows:

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;

Explanation

The correct syntax for an INSERT statement using a SELECT clause requires the SELECT clause to return a single column, not multiple columns enclosed in parentheses.

Additional Considerations

If you continue to encounter issues after correcting the syntax, consider the following:

• Ensure that there are no cross-join conditions in the WHERE clause, which can lead to excessive row combinations.
• Check the data sizes in both the BOOK and temp_cheques tables. Large datasets may require increasing the SQL_BIG_SELECTS system variable to allow for larger queries.

The above is the detailed content of MySQL INSERT ... SELECT Error: Why 'Operand Should Contain 1 Column(s)'?. For more information, please follow other related articles on the PHP Chinese website!