Why Does My MySQL INSERT ... SELECT Statement Result in 'Operand Should Contain 1 Column(s)'?

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

When executing an INSERT statement using a SELECT clause in MySQL, you may encounter the error "Operand should contain 1 column(s)". This error occurs when the SELECT clause returns more than one column, violating the expected format of the INSERT statement.

To address this issue, follow the correct syntax for an INSERT statement with a SELECT clause:

INSERT INTO MyTable  (PriKey, Description)
       SELECT ForeignKey, Description
       FROM SomeView

In your specific query, remove the parentheses from the SELECT clause:

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;

Furthermore, the error "The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay." indicates that the cross-referencing query may result in a large result set. To allow the operation, execute the following statement before running the INSERT query:

SET SQL_BIG_SELECTS = 1

Note that this may still result in a lengthy execution time.

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