4

I asked this question yesterday Funky Delete Issue

Now I'd like to now how to delete safely.

So how do I use commits and transactions and that syntactical sugar to cover my @$$ because yesterday I dumbly deleted 51,000 rows. I had a backup but I still thought HOLY ^%$# that was too easy.

So how do I safely:

    DELETE FROM bBoxHeader
    WHERE bBoxHeader.bHeaderId <> '1099' 
    -- Ooops meant that to be equal. How do I roll back?

How do I wrap that so I don't blow away 51000 rows

Community
  • 1
  • 1
TooMuchToLearn
  • 173
  • 1
  • 3
  • 10
  • Don't you have to name the transaction or will it seriously work to just: BEGIN TRANSACTION: Delete FROM T1 where T1.F1 = T2.F3 COMMIT, OH NO! ROLLBACK? – TooMuchToLearn Jul 10 '13 at 19:04
  • If you COMMIT you can't ROLLBACK, so no, it doesn't work that way. You need to pick one and only one. ROLLBACK is only an undo for transactions that are still active, and a committed transaction is no longer active, it's "in the books." In my career I have yet to come across a use case for naming transactions (though I'm sure they exist, but unlikely to be useful for you). – Aaron Bertrand Jul 10 '13 at 19:27
  • Yeah, I gathered. In my experiments I didn't highlight the commit until I inspected the sample DB to be sure I got the right rows I wanted. I thought it was *VERY* sweet how that all works together. I mean I've used it in SP's and just blindly let it go never really knowing how the ballet went. Now, playing with it in real time in the execution window it's very cool to see how you process the instructions and in what order. – TooMuchToLearn Jul 11 '13 at 20:08
  • What I loved is that I can select counts into a var and then check the results in the var. If I have the right values in the var I can commit and that's cool. Now I have new tools and ideas to cover my @$$. That's always good news. – TooMuchToLearn Jul 11 '13 at 20:11

1 Answers1

16

Whenever you're doing unverified, ad hoc DML against production data, you should always wrap it in a BEGIN TRANSACTION with a subsequent COMMIT and ROLLBACK. If you run it without checking and then realize you messed it up, you can roll it back. Otherwise you can commit it.

BEGIN TRANSACTION;

DELETE ... WHERE ...

-- COMMIT TRANSACTION;
---^^^^^^ if the number of rows affected is correct, highlight this & execute

-- ROLLBACK TRANSACTION;
---^^^^^^^^ otherwise highlight this and execute

Note that this can ALSO cause you to say HOLY whatever because if you forget to run either the rollback or the commit, then go to lunch or go home for the weekend, you might come back to work looking at your pink slip.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 2
    +1, good warning there. (and, for the record, I had nothing to do with you answering here this time) – Lamak Jul 10 '13 at 18:55
  • I still don't understand. Could you write an example for me that forces an error that I can rollback. It would be DELETE FROM Contracts.Contracts WHERE ContractType=6 and Do I highlight the rollback when I execute? Sorry I'm so dumb but I'm trying to figure this out so I don't get a pink slip. – TooMuchToLearn Jul 10 '13 at 19:17
  • 2
    You highlight the BEGIN TRANSACTION and the DELETE and hit F5. If you realize "oh crap!" you highlight the ROLLBACK TRANSACTION (without the leading comment) and hit F5. If the DELETE was right, you highlight the COMMIT TRANSACTION (again, without the leading comment characters) and hit F5. – Aaron Bertrand Jul 10 '13 at 19:19
  • It's a demo DB. I'm screwing around on dumb data. – TooMuchToLearn Jul 10 '13 at 19:29
  • Actually I'm trying to build a simple transaction template. – TooMuchToLearn Jul 10 '13 at 19:30
  • I'm just trying to get a very simple grip on t-sql transactions, rollbacks, etc... I made a backup yesterday on a prod DB and proceeded to accidentally delete 15,000 (not 51,000) rows. I quickly restored and then thought... SELF, IT's time to put your stuff in rollbacks and commits. If that makes me a bad person for asking maybe SO has lost it's edge. Is this a safe place for learners or isn't it? – TooMuchToLearn Jul 10 '13 at 20:42
  • 4
    @TooMuchToLearn nobody said it makes you a bad person for asking. I think the suggestion was based around the fact that if you are responsible for production data, and an accidental delete could cost you your job, your employer is probably being unfair to you by making you responsible for things you don't understand yet. This is absolutely a place where you can gain that understanding, but the comment was addressed at your situation, not your current knowledge. – Aaron Bertrand Jul 10 '13 at 20:45
  • AhA! I got it. I'm so dumb. Geez. Just like the original answer explained. How stupid could I be? Don't answer that. I get it now. Thank you to everyone who responded. I'm a little thick at times. – TooMuchToLearn Jul 10 '13 at 20:51
  • BEGIN TRANSACTION -- Highlight the above. DELETE FROM dbo.bBoxHeader WHERE bBoxId > 100 AND bBoxId < 150 -- Highlight this above too. -- Then hit execute. COMMIT TRANSACTION -- If you get what you want then commit it. ROLLBACK TRANSACTION -- If the results terrify you highlight the rollback. --! !!!HOW FRIGGIN EASY AND SAFE!!! !-- – TooMuchToLearn Jul 10 '13 at 21:12
  • Aaron Bertrand, thank you. I'm very defensive and probably need to take a lude or two. Our situation is such where we don't normally have time to do things the right way. It's said. We just have to run and gun. I work with an excellent DBA and normally I don't make stupid mistakes. But we have to write ad-hoc SQL a lot. I've done transactions in SP's but I didn't know how to execute them AD HOC. It's so easy though. I cannot believe it. I was confused because in my SP's I usually test for errors like referential integrity and such. Errors that return. I was uncertain how to handle ad-hock txs. – TooMuchToLearn Jul 10 '13 at 21:16
  • 1
    Before you step away from your computer, check that `SELECT @@TRANCOUNT;` is 0 - if it's not, the last thing you want to do is be TOO careful and leave a transaction there that could ruin someone else's day. – Aaron Bertrand Jul 10 '13 at 21:17
  • Oh! Okay. That makes sense. I'll take this information into consideration as I continue to pile on knowledge in this vast land of IT we crawl around in. – TooMuchToLearn Jul 11 '13 at 20:06