4 ms·
Always write DELETEs as SELECTs, and once you run the SELECT and it returns the rows you want, then swap out the SELECT * with a DELETE.
by Spoom 9y ago
Always write DELETEs as SELECTs, and once you run the SELECT and it returns the rows you want, then swap out the SELECT * with a DELETE.
- mfoy_ 9y agoIt sounds silly, but that makes a lot of sense. Lots of tips for me to take away from this thread. :)
- wonderwonder 9y agoThat's a really great idea.
- amyjess 9y agoThat's good advice in general, not just for SQL. For example, I often write my 'rm' commands as 'ls' at first and then swap out the command once I know it gets the right thing.
- fapjacks 9y agoYes, I learned this from a sysadmin in the early 90s and did this when learning to use Unix and having used the technique in various other contexts (e.g. database DELETEs), I am confident this one weird trick has saved me from catastrophic mistakes countless times.
- reddit_clone 9y agoYep. I do that too. Along the same lines I always do an 'echo' before I 'find -exec' anything.
- hinkley 9y agoI count to five before hitting enter. I have stopped on 3 or 4 a number of times. 5 seconds is frequently enough time for your brain to catch up with your fingers. And nobody is going to notice it took you 15 seconds to do something instead of 10.
- jsight 9y agoThat is really good advice. I have also found that it is good to do this with file delete commands (ls before rm).
- colanderman 9y agoAlso, open a transaction first.
- koolba 9y agoThat works halfway for UPDATE statements as well. At the very least you can see which rows you’ll be updating. Another tip is to write the WHERE clause and SET clause prior to filling in which table is being updated. That way you can’t accidentally run something prematurely.
- pc86 9y agoUnless, like me, you then highlight the statement without the WHERE clause and run it, realizing your mistake a split second before you hear the key click...
- Tenhundfeld 9y agoI have a couple of SQL habits along these lines that I haven't seen others do. If I'm typing an ad-hoc DELETE or UPDATE statement, I'll slap the WHERE keyword on the end of the main command line. For example: UPDATE items SET active = 0, deleted_at = now() WHERE category = 'Foo' DELETE FROM items WHERE category = 'Foo' AND customer = 123 Yeah, it looks ugly, and I wouldn't use that formatting for "real code," with tests and whatnot. But for messing around in the query editor, I just find that I'm much more likely to accidentally leave off whole lines. I never accidentally select parts of lines. More generally, the idea is to make your partial query invalid syntax, if you leave off the last line. I also tend to wrap destructive DML in comment blocks. So in my query editor, I'd actually have: /* DELETE FROM items WHERE category = 'Foo' */ The idea here is just that I want to be required to select this text and run it. If I have some other queries in the window and somehow accidentally run all statements in the window (didn't select something I meant to or maybe a query is hiding beneath the scroll line), I don't want destructive queries to run. EDIT: I've actually never made a catastrophic mistake of this nature, but there have been some very close calls. And I've seen it happen several times, from excellent programmers who just made a mistake.
- hinkley 9y agoIt's like gun safety. All the rigor feels stupid until you hear stories about people not following the rules.