SQL is awesome. Things that used to take hours to do by hand can be done in seconds with a few commands. Today we discovered that an account had been created that should not have been, and there were 51 records that had been entered in there erroneously. Going into each transaction and manually making the change would be time consuming and tedious.
Thankfully with SQL this can all be done rather quickly with an update statement. With one simple update statement I can move all fifty one of those gifts into the proper account, allowing me to delete the erroneous account. Key point here: when using the update statement in SQL, DO NOT FORGET THE WHERE CLAUSE!
For those of you that read the second half of the title of this entry, you can see where this is going.
I did a select (with where) followed by an update, and forgot to include the where clause in the update statement. Yes, yes, I know... For those of you who don't, in one instant I moved every single financial transaction into the database into one account.
Um... oops?
Thankfully we back the database up every night at 2am, and we had not entered contributions today. So after a little learning on how to restore one table from a backup (and restoring the account I had deleted earlier today) we have our contributions allocated to the right accounts again...
Sigh.
Joel
PS - Even with a really fast box, it takes a while to delete and restore 700,000 rows from a table...
Subscribe to:
Post Comments (Atom)
1 comment:
I think we've all been bitten by that at one point. A trick I learned at an old job (where we were constantly working in a production database) is to work with an open transaction until you're sure you didn't make a mistake.
For example, run this in SQL Management studio:
begin tran
update blah set foo = 1 where bar = 5
then verify that the # of records affected is reasonable by looking at the "Messages" tab or running another select statement.
If everything looks good, run:
commit
If you see you made a mistake, just run:
rollback
Post a Comment