MySQL 语法错误消息“操作数应包含 1 列"

2021-11-20 00:00:00 mysql mysql-error-1241

我尝试运行以下语句:

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;

据我所知，应该将 temp_cheques 中的每条记录插入到凭证中，ID 和 DENOMINATION 字段对应于 BOOK 表中的条目(temp_cheques 来自数据库备份，我正在尝试以不同的格式重新创建)).但是，当我运行它时，出现错误:

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

我在 SQuirrel 中运行它，并且没有遇到任何其他查询的问题.我的查询语法有问题吗?

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?

BOOK的结构是:

ID  int(11)
START_NUMBER    int(11)
UNITS   int(11)
DENOMINATION    double(5,2)

temp_cheques 的结构是:

The structure of temp_cheques is:

ID  int(11)
number  varchar(20)

推荐答案

尝试从 SELECT 子句中删除括号.来自 Microsoft TechNet，使用 SELECT 子句的 INSERT 语句的正确语法如下.

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

<小时>

您得到的错误是SELECT 将检查超过 MAX_JOIN_SIZE 行；检查您的 WHERE 并使用 SET SQL_BIG_SELECTS=1 或 SET SQL_MAX_JOIN_SIZE=# 如果 SELECT 没问题."实际上是正确的，假设您有很多BOOK 和 temp_cheques 中的行.您正在尝试从两个表中查询所有行并进行交叉引用，从而产生 m*n 大小的查询.SQL Server 试图在执行可能较长的操作之前警告您这一点.

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.

在运行此语句之前设置 SQL_BIG_SELECTS = 1，然后再试一次.它应该可以工作，但请注意此操作可能需要很长时间.

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.

相关文章