SQL Server 子查询返回了 1 个以上的值.当子查询跟随(字符)或当子查询用作表达式时，这是不允许的

2021-09-10 00:00:00 sql tsql sql-server

我正在尝试根据出现的情况更新某些字段.如果它们只发生一次，我会更新一些状态字段.

I am trying to update some fields based on their occurence. If they only occur one time, I am updating some status field.

我目前的代码如下:

UPDATE table1
SET statusField = 1
WHERE someID = (
               SELECT someID
               FROM table1
               GROUP BY someID HAVING COUNT(*) = 1
               )

这会返回一个类似于标题中的错误:子查询返回了 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.

是否有其他易于阅读/简单的解决方案?

Is there any other, as easily readable/simple, solution to this?

推荐答案

使用 IN 关键字而不是像这样的等号运算符:

Use IN keyword instead of equals operator like so:

UPDATE table1
SET statusField = 1
WHERE someID IN (
           SELECT someID
           FROM table1
           GROUP BY someID HAVING COUNT(*) = 1
           )

使用 = 要求子查询只返回 1 个结果.IN 关键字作用于列表.

Using = requires that exactly 1 result is returned by the subquery. IN keyword works on a list.

相关文章