SQL Server - update only when values are different

Question

Hi folks,

In SQL Server 2008, how do I update fields in a table only if their values differ with the values in the update statement ?

For example:

I have TableA with column FirstName whose value is 'Roger Moore' with an unique id of '007'. Now, i am calling an update statement but it should update the 'FirstName' field only if value is something else other than 'Roger Moore'.

TIA

Answer 1

update TableA 
set FirstName = 'Roger Moore' 
where ID = '007' 
    and FirstName <> 'Roger Moore' 

Answer 2

Are you talking about an UPDATE statement like this one?

UPDATE TableA
   SET FirstName = 'New Name'
 WHERE FirstName <> 'Roger Moore'

That doesn't take into account your unique ID, but I think it's what you're looking for.

Answer 3

Generally it isn't worth the effort checking for change of a field

http://blogs.msdn.com/queryoptteam/archive/2006/07/07/659453.aspx

Possibly there would be lock benefits by doing the update conditionally.

However, if you insist on doing this, you could also look at doing this 'in the background' with an instead of trigger and comparing each of the inserted vs current values.