4 ms·

I have a very strict habit after once or twice doing this: always start all DELETE commands with "--" (that is, comment it out) until I have written a where-cla

I have a very strict habit after once or twice doing this: always start all DELETE commands with "--" (that is, comment it out) until I have written a where-clause. 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".

leftnode 11y ago
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".
- oneeyedpigeon 11y ago
  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.
spacecowboy_lon 11y ago
I tend to write my DELETE statements by writing a SELECT first then edit that into the delete version
- danieltillett 11y ago
  This is exactly what I do too.
- cwilkes 11y ago
  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.
- sixothree 11y ago
  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.

leftnode 11y ago

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".

oneeyedpigeon 11y ago
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.

spacecowboy_lon 11y ago

I tend to write my DELETE statements by writing a SELECT first then edit that into the delete version

danieltillett 11y ago
This is exactly what I do too.
cwilkes 11y ago
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.
sixothree 11y ago
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.