Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Also look into recovery models, backups, and point in time recovery. –  Martin Smith Mar 30 at 21:20
    
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. –  Greenstone Walker Mar 31 at 9:04

2 Answers 2

up vote 3 down vote accepted

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
share|improve this answer

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.

share|improve this answer
    
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. –  Mark Sinkinson Mar 30 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. –  Mark Wilkinson Mar 30 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"? –  Greenstone Walker Mar 31 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. –  Mark Wilkinson Mar 31 at 10:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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