Rollback an Update statement

[reden55]

Hello,

I have two questions that are related
1.) How does SQL7 manage "rollbacks"
2.) What SQL code do I use to "rollback" a sql transaction that goes awry?

In my test environment, I have run the following script to test moving contacts from companies that will be deleted in a de-duping process. My 'select' statement for the two company id's returned 4 contacts (2 for each company). When I ran the Update statement it updated all 11818 contact records by changing the contact.company_id to Null.

I have created a table (company_de_dupe) with the following columns, dup_company_name[txt], dup_company_id[binary], company_name[binary],company_number[binary]. Into this table I have inserted the dup's and the keepers data.


update contact
set company_id = (select d.company_number from company_de_dupe as d join contact as c
on d.dup_company_id = c.company_id
where dup_company_id in (0x0000000000000259,0x00000000000001C8))

My background is Oracle, and have only been working with SQL server7 for 2 months. I'm used to be able to type "rollback" and undo my last operation. What gives?

Thx for any and all help.
Rick

 

[foxdev]

By default SQL Server uses implicit transactions, so each DML statement is wrapped in its own transaction. Simply issuing a ROLLBACK after the statement has no effect, since the transaction is already committed.

To allow you to selectively ROLLBACK or COMMIT the statement, issue a BEGIN TRAN statement before, then a ROLLBACK or COMMIT afterwards as desired. --------------
Low Cost Prints:

http://www.lowcostprints.com

 

[reden55]

Thankx for the update