Re:An administrator accidentally deleted the production data...
by leftnode on Wednesday March 23, 2016 @07:24AM
(#11343470)
You can also start all of your commands with "BEGIN;" (at least in Postgres) to ensure you're in a transaction you can easily rollback.
Another trick it to start all "DELETE" commands as a "SELECT" to ensure you get the "WHERE" part correct, and then swap out the "SELECT *" with a "DELETE".
Re:An administrator accidentally deleted the production data...
by oneeyedpigeon on Wednesday March 23, 2016 @07:50AM
(#11343583)
I use the latter almost religiously. Also can be useful to add a LIMIT on the end, just to guard against an UPDATE running amok. And I tend to write my queries 'backwards' (e.g. starting with the WHERE clause, ending with the "DELETE", to guard against executing the query prematurely.
Re:An administrator accidentally deleted the production data...
by spacecowboy_lon on Wednesday March 23, 2016 @08:08AM
(#11343668)
I tend to write my DELETE statements by writing a SELECT first then edit that into the delete version
Re:An administrator accidentally deleted the production data...
by cwilkes on Wednesday March 23, 2016 @10:19AM
(#11344664)
I've turned to creating a temp table and putting the primary keys from the select statement into there so I can guard myself against the thing that was to delete a handful of rows deleting everything.
Plus with that you can do another join and see if those rows have some sort of value in it that you didn't expect.
Re:An administrator accidentally deleted the production data...
by sixothree on Wednesday March 23, 2016 @05:03PM
(#11348216)
I also put my DELETE statement on a line by itself and comment out the line. In SQL Server execution processes only the selected text. So I select starting with DELETE then run.
Re:An administrator accidentally deleted the production data...
by danieltillett on Wednesday March 23, 2016 @08:39AM
(#11343856)
This is exactly what I do too.
My command in the buffer might go through these steps:
1. "Delete from" 2. "-- delete from" 3. "-- delete from table where condition limit n;" 4. (Generally either ask a co-worker or make a Jira with the exact command I have at this point, so there's a sanity check and/or permanent record, but for very low risk/especially mundane/especially time critical updates, do it) 5. Delete the "--" and run it. 6. Think hard about adding some functionality in the app for doing it in app code unsteady of in database code.
Generally do the same for "UPDATE".