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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Kevin CrossChief 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.