SQL Server – Proper Way to Update a Table

azure-sql-databasesql-server-2008-r2update

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.

Best Answer

Study the following:

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
Related Question