I'm a fan of the "Write as a SELECT first" method for any DELETE or UPDATE queries, but in my busier no-sleep days, I was very fond of mysql's --i-am-a-dummy switch[1] on the CLI client, which will "Permit only those UPDATE and DELETE statements that specify which rows to modify by using key values."
Although, these days, I generally avoid mucking around with production DBs directly. It's all manually scripted migrations, and testing said scripts on a backup or unused slave, which is by far the safest, and also helps to avoid running queries that might adversely affect production's performance.
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.
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".
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.
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.
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.
I dont use mysql, but it actually has a command line flag to help with this problem that always made me laugh, --i-am-a-dummy
This is an alternate form of the flag --safe-updates, this option prevents MySQL from performing update operations unless a key constraint in the WHERE clause and / or a LIMIT clause are provided.
Several years ago a colleague wrote a handy command line tool that was potentially destructive if misused, so naturally the outsourced operations team misused it. In response he added a flag along the lines of “—-yes-i-really-do-know-what-im-doing” that was undocumented and mandatory for the destructive operations.
My personal preference is to either have a "deleted_at" column so deletes never need to happen, or have a "history" table.
When this isn't possible, I do a select for the PK and delete based upon only the PK. This way I can review the rows to be deleted and back them up manually if needed.
All of this is way overkill considering the backups available these days.
I am a bit less convinced by this practice. The general consensus seems to be: "oh you can SELECT around after you've done your update and before you commit; the update." But while you're doing that, you might be holding row locks on all the data your update touched, and meanwhile the lock acquisitions in your app are failing, along with whatever that entails. And if you're not doing that and immediately commit;ing, are you gaining anything?
When the UPDATE or DELETE finishes it will output the number of rows changed. If that number is much larger than expected, something probably went wrong. So there's a gain even if you don't investigate the results in detail.
I agree with parent that using transactions is good practice. In general, naked SQL on a production DB is a bad idea - it is not different to running untested coded in a production environment. Why risk it?
Transactions are a sane safeguard if you absolutely must run SQL on your production database.
This is a lot of manual steps. Why not have migration scripts in your code base. Run them in dev and qa and staging so you know they work. Then you go to prod and are done.
The less manual steps the less mistakes will happen.
I had a great mentor who taught me all the safety mechanisms.
Start your .sql file with "use xxxxx" (non-existent database name; will prevent execution if you fat-finger F5.)
Always write your deletes as a SELECT first:
SELECT *
-- delete
FROM blah
WHERE blah = 1
Never uncomment the "delete" line; just select the portion of the query after the comment with your mouse and F5 it.
And of course: always design your database with support for soft-deletes, because sooner or later you'll need to add them in.
Yep, I've never done it, but I've definitely been aware of how easy it can happen. When I'm in this kind of situation on a production DB without easily restorable backups I usually try to remember to put all my delete/update queries in a transaction. Then commit after I see the appropriate number of rows have been effected.
I have a story about that based on real facts(TM): I once had to reset my own password on a production database and I decided to hash it by hand and UPDATE my row in the users table.
A few hours later we had got a few calls from angry customers who couldn't log in. I had effectively forgotten the WHERE clause so all users had the same password: mine.
Extra points for not having read the "xxx rows updated" line that the mysql console outputs after each query...
I updated the hash and the salt in the same query. They weren't salted against the user id or anything like that, just a second column for the salt, which is... common practice.
