Rollback in Sql query window
-
Hi,
I use ms sql query analyzer/window to run my queries and do bulk updates / inserts/ deletes as everyone might do.
Sometimes while i am updating a bunch of records there are times when i by mistake write a wrong condition and all records get updated with a particular value. Is there a way to roll back such statements?
I have to deal with a users database of more than 100K and in such situations when say i am udation job titles of people and if such and error occurs it can spell disaster.
Please advise and oblige.
Thanks and regards
Hitendra
-
-
-
Not that I've ever made a mistake myself
, but in QA I tend to precede statements/batches that affect live data with a BEGIN TRANSACTION. It's only after I've confirmed the number of rows affected, or confirmed results within those rows (with a nolock select) that I'll issue a COMMIT TRANSACTION.Cheers,
- Mark
Cheers,
- Mark -
Cross post in different categories ?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
So the statement should basically be like
Begin Transaction
update users set job_title="Manager" where job_title like "%Mgr.%"
update orders set job_title="Manager" where job_title like "%Mgr.%"
And after doing all such updates i issue a Commit statement? If not i just type Rollback to undo the changes?
Am i right?
-
yep!
Sometimes, I will even cover my rear further by doing something before the transaction, such as:
select * into tempdb..orders from orders
Sometime I'll take a prior full backup of the database.
It really all depends on the circumstances.
Cheers,
- Mark
Cheers,
- Mark -
I think mmcork covered about all the bases.
Maybe you can start with a SELECT with a WHERE clause and execute it. Then to the update with a transaction and SELECT AFTER the fact.
I think the under laying problem do not do ad hoc queries on a production system, but we live in a real world.
, but in QA I tend to precede statements/batches that affect live data with a BEGIN TRANSACTION. It's only after I've confirmed the number of rows affected, or confirmed results within those rows (with a nolock select) that I'll issue a COMMIT TRANSACTION.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply