MySQL语法错误消息“操作数应包含1列". [英] MySQL Syntax error message "Operand should contain 1 column(s)"
问题描述
我尝试运行以下语句:
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中的每条记录插入到VOUCHER中,其中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.
这篇关于MySQL语法错误消息“操作数应包含1列".的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!