问题描述
我正在尝试根据出现的情况更新某些字段.如果它们只发生一次,我会更新一些状态字段.
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.