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:
Then re-read this general technique:
Besides using explicit transactions, I typically write a SELECT statement first to see how many rows would be affected, and then copy the WHERE clause to my UPDATE statement.
From this I see 100 rows would be updated.
So I either copy the WHERE clause, or convert the SELECT into an UPDATE
If you use this in conjunction with a explicit transaction you can roll it back if the record count doesn't match between your SELECT and your UPDATE.
This can be a useful habit to get into.