SQL 在此上下文中不允许使用子查询。只允许标量表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22284661/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Subqueries are not allowed in this context. Only scalar expressions are allowed
提问by ConfusedDeer
My customer is running MS SQL Server 2000. I completed a project, but I failed to realize that MS SQL Server 2000 will not allow a select inside insert into values thus giving the error:
我的客户正在运行 MS SQL Server 2000。我完成了一个项目，但我没有意识到 MS SQL Server 2000 不允许在插入值中进行选择，因此出现错误：
Subqueries are not allowed in this context. Only scalar expressions are allowed.
When I run the following query:
当我运行以下查询时：
insert into table_one (greeting_column, name_column)
values (
'hello',
(select column_1 from table_to where name = 'bob')
)
I'm calling this query from coldfusion10. I've already realized a solution utilizing coldFusion10 for this problem by replacing the select statement calling the query and storing the results in a coldFusion list variable, then iterate through a loop that inserts the contents of the CF list variable to its respective record, but this takes much more processing then a simple SQL statement. I've found solution on another web page that got around the issue by doing this (Yes, I know it's bad practice to do a 'select *' this is just an example):
我正在从 Coldfusion10 调用这个查询。我已经通过替换调用查询的 select 语句并将结果存储在 ColdFusion 列表变量中，然后通过循环迭代将 CF 列表变量的内容插入到其各自的记录中，从而实现了使用 ColdFusion10 解决此问题的解决方案，但是这比一个简单的 SQL 语句需要更多的处理。我在另一个网页上找到了解决方案，通过这样做来解决这个问题（是的，我知道做一个“选择 *”是不好的做法，这只是一个例子）：
CREATE PROC whatever
@REC int,
@ChangedIP varchar(15),
@ChangedBY varchar(30)
AS
INSERT INTO table_LOG
SELECT *, GETDATE(), @ChangedID, @ChangedBy FROM table WHERE record = @REC
But I don't think coldFusion will allow Transact-SQL Variables in a query (will try after the weekend) Is there a way to re-write not using Transact-SQL Variables?
但我不认为 ColdFusion 将允许查询中的 Transact-SQL 变量（将在周末后尝试）有没有办法不使用 Transact-SQL 变量来重写？
回答by dnoeth
I don't have a Microsoft SQL Server 2000 anymore, but this should also work, simply replace VALUES with SELECT and remove the brackets:
我不再有 Microsoft SQL Server 2000，但这也应该有效，只需将 VALUES 替换为 SELECT 并删除括号：
insert into table_one (greeting_column, name_column)
SELECT
'hello',
(select column_1 from table_to where name = 'bob')
回答by RBarryYoung
I no longer have SQL Server 2000 to test against, but I have no idea why it would not allow your query, AFAIK, it's perfectly valid. My suspicion is that it's Coldfusion or maybe your access provider that is blocking it.
我不再有 SQL Server 2000 可供测试，但我不知道为什么它不允许您的查询，AFAIK，它完全有效。我怀疑是 Coldfusion 或者您的访问提供商阻止了它。
In any event, I know for sure that the following is valid:
无论如何，我确信以下内容是有效的：
insert into table_one (greeting_column, name_column)
Select 'hello',
column_1
from table_to
where name = 'bob'
