Update Query Question

Hello this is what I am trying to do,

I am updating a field, what I need to do is if what I update is different from what was in the field I need to update a next field called "Changed" = 1.  Any idea how I do this in my update Set statement?
Who is Participating?
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Did you see my first suggestion?  Doesn't need a trigger and you only update the values where new data is different and so you know that changed = 1 applies.  Give that a try.

I guess you could try it in the set.  I didn't suggest that as my thought is your are changing the column value and so didn't think the test would be reliable; however, that is when I thought of it with column you were changing first and then changed = 1.  Try like this, may work for what you want...

UPDATE TableName
SET Changed = CASE ColumnName WHEN @NewValue THEN 0 ELSE 1 END
, ColumnName = @NewValue
Kevin CrossChief Technology OfficerCommented:
Hello sbornstein2,

You can do this through an update trigger.  Or simply do this:

UPDATE TableName
SET ColumnName = @NewValue
, Changed = 1
WHERE ColumnName <> @NewValue

Best regards,

Kevin CrossChief Technology OfficerCommented:
If you have more columns or just prefer using trigger, then here is the information from Microsoft:

The thought there would be on an UPDATE there are two tables: DELETED and INSERTED.  
DELETED --> contains row as it was before change
INSERTED --> contains row as it is after change

Therefore, if the value of the column is different in inserted than in deleted, then records has changed.

Hope that helps.
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

sbornstein2Author Commented:
is there a way to handle it without using a trigger?  I don't want to use a trigger if possible because of performance and the blackbox of it.  I am using an update statement though that I need it to be within the set somehow but still update things even if the old value vs new value are not equal.
Mark WillsTopic AdvisorCommented:
Would be inclined to do :

update tablename set changed = 1, columname = @newvalue
where columname <> @newvalue
and <row_qualifiers_go_here>

might need to check for NULLs
Kevin CrossChief Technology OfficerCommented:
That was my original suggestion, Mark.  Not sure why that didn't work...
Mark WillsTopic AdvisorCommented:

So it was (kind of just glossed over when I saw more postings - sorry about that)... except for the row qualifiers... and equally unsure as to why it didn't work.


Triggers do not automatically mean performance problems, you are doing an extra update query anyway, and may as well be server side and "inside the table"  so to speak.

Not a big fan of having a "changed" column, because what happens next time it changes ? If you are looking for a bit of an audit trail, then the trigger approach could record every change... If you are looking to flag changed rows (for extract, synch, etc) then the flag is fine - so long as it is reset somewhere otherwise you are only really recording / flagging the first change as an "event" (ie changes state from 0 or NULL to a 1).

sbornstein2Author Commented:
worked awesome thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.