CODEWITHSUNDEEP
sql-server
schar
schar

Reputation: 2658

SQL query - Can I not do this?

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.

Upvotes: 2

Views: 56

Answers (2)

Michael Moreno
Michael Moreno

Reputation: 181

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.

Upvotes: 1

rsbarro
rsbarro

Reputation: 27349

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:

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.

Upvotes: 3

Related Questions