CODEWITHSUNDEEP
sqlsql-server-2008sql-update
SoftwareGeek
SoftwareGeek

Reputation: 15782

SQL Server - update only when values are different

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

Upvotes: 2

Views: 10757

Answers (3)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171579

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

Upvotes: 7

StuartLC
StuartLC

Reputation: 107387

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.

Upvotes: 2

SqlRyan
SqlRyan

Reputation: 33944

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.

Upvotes: 0

Related Questions