4 ms·
The habit I learned was: before running any DELETE or UPDATE statement, run a SELECT with the same WHERE. (e.g. if I meant to say DELETE FROM puppies WHERE cut
by samstokes 15y ago
The habit I learned was: before running any DELETE or UPDATE statement, run a SELECT with the same WHERE. (e.g. if I meant to say DELETE FROM puppies WHERE cute = 0, first run SELECT FROM puppies WHERE cute = 0.)
I find I remember to do that because of the direct benefit (getting a sneak preview of what I'm going to delete), but it also means I end up thinking twice about the WHERE statement, so I'm much less likely to miss it out or get it dramatically wrong.
- DrPhish 15y agoI'm a couple of levels more paranoid than that. First, I'll write the DELETE as a regular SELECT (to preview number of rows), then turn it into a SELECT INTO to save the soon-to-be-deleted rows into a table with a backup_date_ prefix (So old backups can be deleted occasionally). Next, before changing anything, I wrap the statement in a BEGIN TRAN, and ROLLBACK TRAN. After all that, I will finally modify the SELECT INTO into a DELETE statement, run it once while wrapped in the transaction (to verify that the number of rows modified hasn't changed), and then finally run without the transaction to delete the rows. Overkill?
- trafficlight 15y agoPossibly overkill. But at least there won't be any tears.
- derekp7 15y agoWhat? No volume snapshot first?
- paufernandez 15y agoI do that for 'rm'. I do an 'ls' first, then reedit the command and put an 'rm'.
- Vivtek 15y agoOoh! I'm going to start doing that.
- augustflanagan 15y agoI do the same thing. Not sure that it has ever saved me from a disaster, but I do like the sneak peak and am DELETE FROM disaster free. knocking on my desk
- Johngibb 15y agoI've always written my sensitive delete queries like this: select * -- delete x from Table x where x.whatever = 1 That way by default it's just a select, and then AFTER you verify the result set you can highlight from the delete on and then run the query (as long as you're in a shell that will run only the highlighted part. I was working in SMS.) This was a common idiom where I worked.
- agilebyte 15y agoGood tip. Unfortunately when I was working as a PHP programmer, I once made a typo in a variable name, a variable that had my WHERE clause in DELETE... Back then I fixed it with a box of chocolates and flowers.