mysql错误消息1,MySQL语法错误消息“操作数应包含1列”。

I tried running the following statement:

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;

which, as I understand it, should insert into VOUCHER each record from temp_cheques with the ID and DENOMINATION fields corresponding to entries in the BOOK table (temp_cheques comes from a database backup, which I'm trying to recreate in a different format). However, when I run it, I get an error:

Error: Operand should contain 1 column(s)

SQLState: 21000

ErrorCode: 1241

I'm running this in SQuirrel and have not had issues with any other queries. Is there something wrong with the syntax of my query?

EDIT:

The structure of BOOK is:

ID int(11)

START_NUMBER int(11)

UNITS int(11)

DENOMINATION double(5,2)

The structure of temp_cheques is:

ID int(11)

number varchar(20)

解决方案

Try removing the parenthesis from the SELECT clause. From Microsoft TechNet, the correct syntax for an INSERT statement using a SELECT clause is the following.

INSERT INTO MyTable (PriKey, Description)

SELECT ForeignKey, Description

FROM SomeView

The error you're getting, "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.", is actually correct, assuming you have many rows in both BOOK and temp_cheques. You are trying to query all rows from both tables and make a cross-reference, resulting in an m*n size query. SQL Server is trying to warn you of this, before performing a potentially long operation.

Set SQL_BIG_SELECTS = 1 before running this statement, and try again. It should work, but note that this operation may take a long time.