The Joel on Software Discussion Group (CLOSED)

I just executed a query in Query Analyzer that was this:
UPDATE table
SET ParticularDate = '11/14/2005'

I forgot to add this one little piece of info:
WHERE Username = 'foo'

I got a message back: 1300 rows affected.  Is there anyway for me to easily undo this?  I can't do a restore from backup, because I can't lose all the info from today.  Is there a way of rolling back the transaction by looking in the event log?

It's not a critical thing that I messed up, but there were some nulls in that column that I wanted to retain. 

Thanks.

If you're using the full backup model, do a full and then transaction backup, and restore (from the full and then the tran log) to a time right before you did that.

I'll wager that many people here have made this mistake (I have). Hope you had a backup.

I believe we're backing up once at night.

(BTW: Use STOPAT when restoring the transaction log)

I should mention that all of the information to recover from the problem is in the log - but unfortunately SQL Server doesn't offer any method of using it apart from backing it up and restoring to a point in time. There are third party tools that can do what you want though (saying "whooops! Undo that transaction." (if you didn't explicitly create a trans then the statement itself was one).

http://www.lumigent.com/products/le_sql.html

*Everybody's* done this. Don't let 'em lie to you -- we've all done it. The trick is to *not* repeat it.

As far as restoring -- you're on your own. Depends on the recovery model you're using as to whether you can do the ol' "point in time" restore or not.

Now, how to avoid it in the future: For *every* query you run in QA, begin a transaction, but don't commit it until you've had time to review the results and see that it makes sense -- the "(1300 rows affected)" should have been sufficient to give you a big clue. I begin every query like this:

BEGIN TRAN

UPDATE tblWhatEver SET SomeField = SomeValue

NOTE: Please notice their's no COMMIT at the end of this. The transaction will hang open until you actually issue a COMMIT statement (or a ROLLBACK statement to undo it).

I always -- even for SELECTS do this -- how many times does one write a SELECT first, to make sure we've got the correct records, and then edit the SELECT so it's a DELETE or UPDATE? Safest to *always* do the BEGIN TRAN first. Let it hang as an open transaction while you check things out, and if it's AOK, then issue the COMMIT. If it's hosed up, issue the ROLLBACK.

Do things this way and you'll never have the problem again in the future.

"Do things this way and you'll never have the problem again in the future."

Unless you're using a common database and all of your coworkers are wondering why they keep getting blocked :-)

BEGIN TRAN will be part of my statements from now on.

I'm glad I learned this on something minor like this - I guarantee I won't forget this...

Nothing is better then hosing your database with a mis-typed where clause on a friday night before leaving.  There is nothing quite like the cold sweat that immediatly breaks out as you realize you just screwed up big time and you are not going anywhere for a while.

Another nice-practices technique I've seen is to train yourself always to write UPDATEs in this order:

UPDATE something
WHERE condition
SET whatever

Yes, everyone's done this.  When I did this, I restored from backup to a different database.  I then copied only the single column from one database into another.  That way, all the new data was intact.  This may or may not work in this situation, though.

Another thing I have learnt is to write out my SQL in a separate text editor before pasting it into whatever SQL administration interface I use.  The transaction idea is great (and, AFTER the fact, obvious) as well.

Marco Arment,

I _believe_ you cannot write your update statement in that order according to standard SQL.  I can't readily find a reference to corroborate this, however.

Marco!  I have been working with SQL for 5+ yrs and I didn't realize you could write queries in the form of
UPDATE foo
WHERE bar
SET bat = baz

That's great.  I'll definitely put that to use also.

I still recall (in nightmares) this one time where I had to move a _really_ big customer's data to our software. The deadline was looming and I was staying the night in their offices. I was sick and it was a miserable time in my life.

So I finally got the import going. It took about 4 hours. I then wanted to test the system with a username with lots of special characters in it:

Update reallyBigDatabase set username='AY';SDFOÖASNFGS§$"§' where ID = 4;

This was mySQL. No rollback.

I proceeded to jump out the window. It was a 1st level office unfortunately.

(yes, I really jumped out the window since it was the only way to get out of the place at night. But only after I did the import again).

I've read that all good admins have deleted a very very important file exactly once. :)

My story: In /etc on a server, trying to fix some problem and wanted to get rid of all the ~ files to make my 'ls' a little cleaner. So I did: 'rm * ~'. After I reinstalled, I *never* did that again.

A client: botched password reset.... "UPDATE user SET password='abc123'".... forgot an important part :)

You're not the only one.

All my deletes and updates end with a LIMIT 1 clause. That way the damage is contained to one record.

But I too have truncated entire tables by accident, luckily only in development environments.

>> Another nice-practices technique I've seen is to train yourself always to write UPDATEs in this order:

Doesn't work with MS SQL Server 2000:
"Incorrect syntax near the keyword 'where'."

BTW, the syntax

UPDATE <table>
 WHERE <codition>
  SET <values>

doesn't work for me. I am using SQL 2000. What gives?

Been there, done that. I've had exactly one bad accident with SQL DELETE, and exactly one bad accident with rm -rf. Like others I've adopted the habit of writing updates and deletes first as selects, then transforming them into updates and deletes if the results look good.

The transaction approach is new to me. I'll be doing that from now on.

It should be

UPDATE <TABLE>
SET <COLUMN=VALUES>
WHERE <CONDITIONS>

data,

I am aware about the conventional syntax. Marco posted earlier in this thread the above syntax, which if working can be a good way to avoid the disaster experienced by OP.

My first day back from a trip a couple weeks back, I did a "DELETE FROM table WHERE id - 627"

In mysql, this always validates to true.  120k items gone.

No default transactions and rollback in SQL Server, eh?  (i.e. auto-commit off)  Score one for Oracle!

My normative "safe" technique for single row updates is

BEGIN TRAN

UPDATE/DELETE DML

IF @@ROWCOUNT = 1
BEGIN
  COMMIT TRAN
END ELSE
BEGIN
  ROLLBACK TRAN
END

I always stick an 'xxx' in front of my delete/update/insert statements.
  i.e. xxxdelete from test where x=10
that forces me to visually inspect it and then I highlight the "delete ..." and hit execute. 

The other thing this buys you is if you have that delete/update/insert query sitting in the window and you accidently hit execute without highlighting anything- you get an invalid sql statement instead of doing a series of deletes/updates when you meant to do that benign select statement

OR set Query Analyzer to create automatic transactions.

Go to Tools -> Options -> Connection Properties Tab.

Check 'Set implicit transactions' item.

Yup done this too...
I ended up restoring the backup from the night before to a test server.
Then Updating the effected rows/columns from the test database that contained last nights info.

This may not work for you but it worked for me because the rows in that table were not updated very often.
I also had to act quick since I also updated the timestamps and had to keep these records from being replicated to clients...luckily nobody had replicated before I fixed it.

I've found the best way to write UPDATEs and DELETEs is to write them as a SELECT first to see the rows that will be effected.  Then modify them into the update or delete.

And somtimes I also do a SELECT * INTO tmpMyTable FROM myTable ( if the table is not that big).  Just so I could revert back to the old table if there was a problem.

In Oracle you can

UPDATE
 (SELECT cola AS a
  FROM table_a
  WHERE c = d)
SET a = e;

This can be very handy when updating from another table:

UPDATE
  (SELECT a.z AS z1, b.z AS z2
  FROM a, b
  WHERE a.y = b.y)
SET z1 = z2;

But I see that Postgres rejects this syntax.

So back to the original question, in "Query Analyzer" is there a rollback option?  and if not WHY THE HELL NOT?

BLANK:

Yes, you have an option to start implicit transactions in Query Analyzer. Please read my earlier post.

"and if not WHY THE HELL NOT?"

because database performance would go to hell if people were creating lots of open transactions and locking lots of tables because they didn't realize their SELECT statement had "begin transaction" prepended to it automatically?

the best defense i've found is AQT; if you're on a windoze machine.  you can set it to issue an update warning at whatever threshold row count you want.  saved my worthless butt more than once.  talks to just about every databaae worth talking to.

"because database performance would go to hell if people were creating lots of open transactions and locking lots of tables because they didn't realize their SELECT statement had "begin transaction" prepended to it automatically?"

Thats 2-0 to Oracle, Oracle never locks for selects and in most cases not for updates either.

Personally I always write my update statements as select count(*) first, that way I know how many rows I'll be updating if I run it for real, but being an Oracle developer I have the option of rolling back if I need to anyway.

For a more interesting comparison of SQL Server and Oracle locking schemes than the simplistic analysis offered by Andrew, see here:
http://www.dbazine.com/db2/db2-disarticles/gulutzan6

I used to be an SQL Server junkie, then was forced to Oracle due to career direction. At first I _hated_ that Oracle query tools default to AUTO_COMMIT=off. Time after time I puzzled over why my db changes were not being picked up by the code.

But some of the bitter war stories in this thread make me sort of grateful towards Oracle's AUTO_COMMENT=off approach... :-)

Thanks Mark

I think Marco meant that the clauses should be written in that chronological order, rather than typed into the prompt in that order.

Or, if not, I claim priority, because that's the technique I use. :-)

Everytime you type "UPDATE", type "WHERE".

Simple. Effective.

Back to the TPS reports...

You didn't state what database you were using.

If it was something like Sybase or SQL Server, then unless you have fipsflagger enabled to put it into strict SQL mode, every like is a seperate transaction and unless you have a backup there is no solution.  If on the other hand your bosses had a better lunch with the oracle sales people, then oracle, besides being slow and buggy, and a few other expletives requires a commit tran to make every permanent.

I just love how no two database manufacturers can take the standard and generate products that require developers to write different stored procedures and also different transaction strategies based upon the RMDBS.

My epic SQL horror story is related here:
http://sleeksoft.co.uk/public/techblog/articles/20051117_1.html