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?
sbornstein2Asked:
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
0
 
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,

mwvisa1
0
 
Kevin CrossChief Technology OfficerCommented:
If you have more columns or just prefer using trigger, then here is the information from Microsoft:
http://msdn.microsoft.com/en-us/library/ms189799.aspx

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.
0
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.
0
 
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
0
 
Kevin CrossChief Technology OfficerCommented:
That was my original suggestion, Mark.  Not sure why that didn't work...
0
 
Mark WillsTopic AdvisorCommented:
@mwvisa1:

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.

@sbornstein2:

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).

0
 
sbornstein2Author Commented:
worked awesome thanks
0
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.