CODEWITHSUNDEEP
sqlsql-server
90abyss
90abyss

Reputation: 7337

SQL: Update a field only if a condition is met

I want to update a column only if a condition is met. So for the column "Type", I want to change its value to "MongoDB" only if its current value isn't "MongoDB" This is what I'm using:

UPDATE Report
    SET Type = 
    CASE
       WHEN Type <> 'MongoDB' THEN 'MongoDB'
       ELSE Type
    END
    WHERE Id = x

The problem is: Even when the Type is "MongoDB" I still see

(1 row(s) affected)

in my SQL result. The whole point of this exercise was to reduce db operations when no needed. Why is it still modifying the record when the condition is not met?

Thanks.

Upvotes: 8

Views: 10630

Answers (1)

Igor
Igor

Reputation: 62213

Why not simplify it like this?

UPDATE Report
SET Type = 'MongoDB'
WHERE Id = x AND Type <> 'MongoDB'

But to answer your question you are still setting a records value even though its to the existing value. The record also comes back in the where clause so you will always have 1 row affected regardless of your CASE statement.

Upvotes: 15

Related Questions