SQL查询——我可以不这样做吗?

update TABLE set A = (
SELECT
    CASE
        WHEN B - C >= A  THEN A
        WHEN B - C <  A THEN B - C
    END AS A
from 
TABLE )

The response I get is :

我得到的回答是:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

子查询返回超过一个值。当子查询后跟=、!=、<、<=、>、>=或子查询用作表达式时，不允许这样做。

2 个解决方案

#1

No, that won't work if your table has more than 1 record. In this case, I'm not sure why you are using a subquery. I think you're looking for something like this:

不，如果您的表有超过1个记录，那么这将不起作用。在这种情况下，我不确定为什么要使用子查询。我认为你在寻找这样的东西:

UPDATE MyTable
SET A = 
    CASE
        WHEN B - C >= A THEN A
        ELSE B - C
    END

This query will update the column A for every record in the table. If B - C is greater than or equal to A, the value of A will be unchanged. Otherwise, A will be set to B - C. I would also use an ELSE instead of two WHENs.

该查询将为表中的每个记录更新列A。如果B - C大于或等于A，则A的值不变。否则，A将被设置为B - c，我也将使用ELSE而不是two WHENs。

#2

the problem you have here is that you do not specify the primary keys in the subquery to match the primary key of the row updated. Hence it returns all the rows matching the condition which is what the error message is referring to.

这里的问题是，您没有在子查询中指定主键来匹配已更新的行的主键。因此它返回与错误消息所指的条件匹配的所有行。

Probably your code should read:

您的代码应该是:

UPDATE TableABC
SET A = CASE
          WHEN B - C >= A THEN A
          WHEN B - C <  A THEN B - C
        END

But your SQL being incomplete I cannot be sure.

但是你的SQL是不完整的，我不能确定。

#1