Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update Query Question

Posted on 2008-11-14
8
Medium Priority
?
174 Views
Last Modified: 2013-11-05
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?
0
Comment
Question by:sbornstein2
  • 4
  • 2
  • 2
8 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22966723
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22966729
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
 

Author Comment

by:sbornstein2
ID: 22969224
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 22969344
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22971334
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22972337
That was my original suggestion, Mark.  Not sure why that didn't work...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22972359
@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
 

Author Closing Comment

by:sbornstein2
ID: 31517049
worked awesome thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question