Life Savers [ BEGIN TRAN ]

Starting with this post, I am planning to do a series titled 'Life Savers' ... tips, tricks, helpers that I learn over the course of my life that are simple, small and yet powerful. I use them day-in and day-out..make my life so much more efficient and managable.

Simple Problem Scenario :
You are to run an UPDATE statement in the Query Analyzer to change the address of a person with AddressID = 1

You try to craft the UPDATE query. Before you do that, it is always a good practice to do an equivalent SELECT statement. So our SELECT query is going to look like this

USE AdventureWorks
GO

SELECT *
FROM Person.Address
WHERE AddressID = 1

(1 row(s) affected)

Now for the UPDATE query

--SELECT *
--FROM Person.Address
UPDATE Person.Address
SET AddressLine1 = '6553 MapleWood Dr '
WHERE AddressID = 1

You run the update and the result window shows
(19614 row(s) affected)

Oops! You were expecting only one row to get updated. To your anguish you realize you forgot to highlight the filter part when you ran the update. Alas ! you are in a big mess now and potentially looking at a long day ahead of you.

Now for our little trick that would have avoided this pitfall.

Before you do any database UPDATE from Query Analyzer, ALWAYS ALWAYS start it with a BEGIN TRAN

BEGIN TRAN

--SELECT *
--FROM Person.Address
UPDATE Person.Address
SET AddressLine1 = '6553 MapleWood Dr '
WHERE AddressID = 1

-- COMMIT TRAN (Or) ROLLBACK TRAN

If the number of rows affected are equivalent to what the SELECT statement gave you, go ahead and do the COMMIT TRAN.

If you see unexpected results like above, do a ROLLBACK TRAN instead.

These 3 simple, amazing lines prevent accidental UPDATEs and can save you tons of time and headache.

Always Recommended.

Update 2007.04.07 : I re-thought about the 'Life Savers' title and in retrospect it seems a little too intense for the topics I am planning to cover under this series. So I will probably be dropping the naming convention in future posts and use something of a milder nature.