90abyss 90abyss - 1 year ago 56
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:

SET Type =
WHEN Type <> 'MongoDB' THEN 'MongoDB'
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?


Answer Source

Why not simplify it like this?

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download