SQL Server 子查询返回了 1 个以上的值。当子查询跟随（字符）或当子查询用作表达式时，这是不允许的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22909737/
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
SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows (chars) or when the subquery is used as an expression
提问by Nict
I am trying to update some fields based on their occurence. If they only occur one time, I am updating some status field.
我正在尝试根据它们的出现更新一些字段。如果它们只发生一次，我正在更新一些状态字段。
My current code is as follows:
我目前的代码如下：
UPDATE table1
SET statusField = 1
WHERE someID = (
SELECT someID
FROM table1
GROUP BY someID HAVING COUNT(*) = 1
)
This returns an error like the one in the title: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
这将返回类似于标题中的错误： Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Is there any other, as easily readable/simple, solution to this?
有没有其他易于阅读/简单的解决方案？
回答by shree.pat18
Use INkeyword instead of equals operator like so:
使用IN关键字代替等于运算符，如下所示：
UPDATE table1
SET statusField = 1
WHERE someID IN (
SELECT someID
FROM table1
GROUP BY someID HAVING COUNT(*) = 1
)
Using =requires that exactly 1 result is returned by the subquery. INkeyword works on a list.
使用=要求子查询只返回 1 个结果。IN关键字适用于列表。
回答by t-clausen.dk
You should join your tables in the subselect. It is possible to use 'in', but in your case I would use exists:
您应该在子选择中加入您的表。可以使用“in”，但在您的情况下，我会使用exists：
UPDATE table1 x
SET statusField = 1
WHERE exists (
SELECT null
FROM table1
WHERE x.someID = someID
GROUP BY someID
HAVING COUNT(*) = 1
)
For better performance I would use this script instead (sqlserver-2008+):
为了获得更好的性能，我会改用这个脚本（sqlserver-2008+）：
;WITH x as
(
SELECT rc = count() over (partition by someID), statusField
FROM table1
)
UPDATE x
SET statusField = 1
WHERE rc = 1
回答by Vignesh Kumar A
Try this
尝试这个
Use Top
使用顶部
UPDATE table1
SET statusField = 1
WHERE someID = (
SELECT TOP 1 someID
FROM table1
GROUP BY someID HAVING COUNT(*) = 1
)
Or you can use INclause
或者你可以使用IN子句
UPDATE table1
SET statusField = 1
WHERE someID IN (
SELECT someID
FROM table1
GROUP BY someID HAVING COUNT(*) = 1
)
