0

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.

2
  • 1
    Also look into recovery models, backups, and point in time recovery. Commented Mar 30, 2014 at 21:20
  • 1
    Stop doing updates. Seriously, why are you doing updates in T-SQL? You should only be doing that in emergency situations. Normal updates should be done through the database client or perhaps through an ETL process. If you do need to perform the updates, however, a backup and restore to test is good because it doesn't lock or slowdown the production database. Commented Mar 31, 2014 at 9:04

2 Answers 2

3

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
2

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.

SELECT * FROM [SomeTable] WHERE [SomeDateColumn] > '3/1/2014'

From this I see 100 rows would be updated.

So I either copy the WHERE clause, or convert the SELECT into an UPDATE

UPDATE [SomeTable] SET [SomeIntColumn] = 8 WHERE [SomeDateColumn] > '3/1/2014'

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.

4
  • Just a general comment, but I really would advise against writing your dates like that because the format is ambiguous. Is it dd/mm/yyyy or mm/dd/yyyy? Go for 'yyyy-mm-dd' and you'll have fewer problems down the line. Commented Mar 30, 2014 at 16:55
  • At my current workplace every one gets confused if i don't format the date as mm/dd/yyyy. So I have formed a habit of doing it that way. I typically prefer yyyy-mm-dd. Commented Mar 30, 2014 at 16:59
  • Personally, I think everyone at your workplace being confused is a better situation than the database server being confused. If your workmates can't handle ANSI-ish dates then how about "Apr 02, 2013" or "2 Apr 2013"? Commented Mar 31, 2014 at 9:02
  • How is this conversation adding to the question originally asked? If this question was about how the date should be formatted in a WHERE clause this conversation might help, but that is not the case. Commented Mar 31, 2014 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.