For SQL Server, What is the proper way to update a table? I don't have a mentor to ask so I am seeking direction from the DBA community. I am in charge of a SQL SERVER 2008 R2 and SQL Azure databases that are the resources for a website and an ERP system for a small company. I've already made the mistake of updating without having a WHERE statement (luckily it only affected a table w/ 30 rows). But with some table containing 20K+ rows, what is the proper way to update tables in SQL Server? Do I create a test DB copy and perform the update on that first (but this doesn't have the website or ERP connected to it, so I can see the
results in regards to those), or is the TRANSACTION function the direction I should study and learn wit it's ROLLBACK feature that seems might be the right way, Or maybe there is some other process all SQL SERVER DBA follow. Any and all advice will be appreciated.
Study the following:


BEGIN TRANSACTION, ROLLBACK TRANSACTION, COMMIT TRANSACTION

Also read Paul Randall's A SQL Server DBA myth a day: (26/30) nested transactions are real
Also read XACT_ABORT

@@ROWCOUNT
IF ...ELSE
GOTO
@@TRANCOUNT
More advanced topics, optional until they're not:

SET TRANSACTION ISOLATION LEVEL
Gail Shaw's intro to deadlocks



Then re-read this general technique:


At the start of the code, GOTO :TheEnd

partial protection against a few "only run part of the code, not all the code" mistakes

At the end of the code, GOTO :TheBeginning

partial protection against a few "only run part of the code, not all the code" mistakes

SET XACT_ABORT ON and start a transaction
Code a single DML statement with appropriate WHERE and JOIN clauses
Code a check how many rows it affected; if it's not exactly as many rows as you expected, roll back the transaction.

repeat the above coding as many times as you have statements

default to rolling back the transaction.

code the COMMIT only after you're sure it's all correct.
run the COMMIT only after getting a cup of coffee, in case you have a moment of fridge horror

Check to verify transaction count is 0