how to Undo and update to field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to Undo and update to field

Author

Topic

Pasi
Posting Yak Master

166 Posts

Posted - 2014-01-17 : 16:31:58

HI,
I have update a table field and I like to know how do you "undo" the update? I set the field_length from 6 to 7 and like to undo this.

Thanks!
Pasi

Here is my command:

table = template_fileds
column= field_length.
filed name= field_name.

update template_fields set field_length='7' where field_name='assesscode9'

chadmat
The Chadinator

1974 Posts

Posted - 2014-01-17 : 17:45:24

If you did a BEGIN TRAN before the statement, you could issue a rollback. But if you didn't, it automatically commits. In that case, there is no way to "Undo" the command aside from restoring from backup up to the Point in time just before you ran the update (Assuming you are in Full recovery).

If you just want to change everything with field_name = 'assesscode9' to 6, you could run:

update template_fields set field_length='6' where field_name='assesscode9'

But that might affect rows that weren't affected by the original statement (If there were rows where field_length was something other than 6 prior to the update, it won't return to the old value)

HTH,

-Chad
Microsoft Certified Master SQL Server 2008

Pasi
Posting Yak Master

166 Posts

Posted - 2014-01-17 : 17:55:34

Thank you chad!!!

Pasi
Posting Yak Master

166 Posts

Posted - 2014-01-17 : 17:59:14

OK So Chad, How do I roll it back? Can you send me example as how to update it using Begin TRAN and then roll it back?
Thanks!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-17 : 18:32:18

[CODE]begin trans
update MyTable
set blah = stuff

if <<Happy>>
commit tran
else
rollback tran[/CODE]You'll need to define what constitutes "Happy", of course.

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

Pasi
Posting Yak Master

166 Posts

Posted - 2014-01-17 : 19:03:57

Thank you!!

Kristen
Test

22859 Posts

Posted - 2014-01-20 : 08:44:54

For Snippets of code that I have (in a file) I write them like this:


begin trans
update MyTable
set blah = stuff
PRINT 'Commit or Rollback required'
-- commit tran
-- rollback tran

because the Commit / Rollback are commented out neither run automatically, I can then select just the statement I want (without including the "--" comment marker in the selection) and press EXECUTE

Pasi
Posting Yak Master

166 Posts

Posted - 2014-01-20 : 11:12:47

Thanks Kristen!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources