90abyss 90abyss - 4 months ago 9
SQL Question

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.

Answer

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.