6 ms·
I hate that feeling you get in the pit of your stomach after you realize what's happening. Same thing when you forget the WHERE on a DELETE FROM.
by trafficlight 15y ago
I hate that feeling you get in the pit of your stomach after you realize what's happening.
Same thing when you forget the WHERE on a DELETE FROM.
- tomjen3 15y agoUse the Dummy switch: http://sql-info.de/mysql/notes/I-am-a-dummy.html http://sql-info.de/mysql/notes/I-am-a-dummy.html
- illumin8 15y agoSweet! Just "alias mysql='mysql --i-am-a-dummy'" in /etc/profile and never make mistakes again!
- jerf 15y agoOver the years I've been steadily training myself to type "WHERE" earlier in the process, until I have finally settled on the obvious-in-hindsight solution: Always simply start with the WHERE clause. (Of course every effort not to be on the SQL shell of a production server in the first place should be taken, but sometimes you need a sledgehammer and nothing else will work.)
- trafficlight 15y agoI do that now, too. I have a trigger-happy semicolon finger.
- moheeb 15y agoMy method is to write each DELETE as a SELECT first. This has the benefit of actually verifying the DELETE you were about to write.
- MatthewPhillips 15y agoDoesn't work when you're writing a stored proc.
- strickjb9 15y agoSame - I now write DELETE FROM WHERE and then fill in the blanks.
- samstokes 15y agoThe 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.
- 15y ago
- foresterh 15y agoI always (with sql server at least) add a begin tran/commit/rollback before any prod statements, because of getting burned in the past. Even if you add the WHERE, but put it on a second line and only run the first, the transaction will help... Of course, if it's going to lock the data, do all of the statements together: BEGIN TRAN UPDATE ... WHERE ... SELECT ... WHERE .... -- show that the update worked ROLLBACK Then run the actual statement
- RyanMcGreal 15y agoI made that mistake once. My DBA never let me live it down. Thank goodness for daily backups.
- blhack 15y agoI wish there was a way to put a limit on table updates in mysql's config file. "Only let me update 5 rows at a time." or somesuch. I now usually type the command out-of-order. So I'll type: where user_id = $f00 limit 10; then prepend the "update foo set bar = " to it...
- DenisM 15y agoBEGIN TRAN DELETE FROM ... SELECT FROM ... ROLLBACK TRAN -- things look bad, itchy finger, etc COMMIT TRAN -- things look good, skip previous line That, and I have 15-minute backups for all databases I care about.
- r00fus 15y agoDELETE FROM? Try TRUNCATE TABLE CUSTOMER ... that one skips the transaction log too, no ROLLBACK possible.
- MatthewPhillips 15y agoYou can't accidentally write a TRUNCATE statement though.
- deleted 15y ago[deleted]
- earl 15y agoSQL is desperately in need of some updates. For starters, DELETE FROM should never be allowed without a WHERE clause. DBs should simply define that as malformed sql.
- ithkuil 15y agoor at least interactive sql shells could ask you if you are really sure
- mikeklaas 15y agoExactly. There is no need given TRUNCATE exists.
- user24 15y agowell, the only difference is that truncate also resets the auto-increment to zero. But you could allow where 1=1 to make it explicit if people really wanted an unbounded DELETE FROM.
- fmx 15y agoNo entirely. DELETE will work with cascading foreign keys, while TRUNCATE will not, at least on SQL Server. Also, DELETE is logged and (I believe) TRUNCATE is not. Having said that, I agree that a WHERE clause should be required - you can always say "WHERE 1=1" or similar if you really mean to delete all rows.
- ignifero 15y agoFor me it's not the stomach. First, breath stops, then for a few seconds numbness in chest and jaw, then face turns pale and soon red. A small nausea follows and then regret sets in. The rest of the day is ruined.
- trafficlight 15y agoHow eloquent.