Safer Deletes and Updates on Live DataBase

05.21.09

Problem: You need to change production data from a database.

Solution: Use Transations.

Its always scary working with live data I found that I feel more confident when I take the following measures.

Do a Select first to check the where clause:

SELECT * FROM Foo WHERE FooID = 1000

Wrap your changes in a Transaction and roll it back if you did not get the expected result.

Begin Transaction
  DELETE FROM Foo WHERE FooID = 1000
IF @@RowCount <> 1 BEGIN
  Rollback Transaction
END
ELSE
  Commit Transaction