Post reply

Rollback in Sql query window

  • August 20, 2003 at 6:08 am

    #64811

    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

  • August 20, 2003 at 6:11 am

    #470566

    -

  • August 20, 2003 at 6:14 am

    #470568

    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

  • August 20, 2003 at 6:14 am

    #470567

    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]

  • August 20, 2003 at 6:20 am

    #470569

    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?

  • August 20, 2003 at 6:27 am

    #470572

    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

  • August 20, 2003 at 2:57 pm

    #470671

    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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply