Pro tip for DELETE queries
If you're ever running something like this

DELETE FROM bigtable WHERE secretfield = 1;

in production the way I always do this is to type out the query without the table name ("bigtable") or the condition ("1").

That means if you accidentally hit Enter at an inopportune moment e.g. after just DELETE FROM bigtable (which would delete all rows!) the query will fail because it's syntactically invalid.

I then go back and add in the table name with a sanity check of "is this the table I really mean?" followed by the same with the conditions. Pause. Then press Enter.

Takes about 5 seconds longer and I've never once deleted something inadvertently in production. Oh, and never copy/paste queries for exactly this reason - unless you modify them to remove the same params first. //

Re: Pro tip for DELETE queries
or you could just start with SELECT * , and see if the results correspond with what you wanted to delete

then bring the DELETE in once you're happy the filters/ table/ db are all correct

or at the minimum type the WHERE field = 1 first to avoid accidentally hitting enter and doing a wholesale delete with no filter. //

Pro tip for DELETE queries
Always always do the Select Count first, then run at least three queries - select count, delete, select count and select any other useful where to prove the delete or the surviving records - all wrapped in a transaction to give yourself chance to check that you’re getting what you expect. Then go for a coffee, come back and check it again before swapping rollback for commit. //

Pro tip for DELETE queries
I can appreciate your imagination, but there are just too many steps where things can completely go wrong.

The only solution that works is to wrap things in a transaction. That's what they are there for, after all.

Certainly, checking the number of rows delete is an important sanity check. But not the only thing that might be checked. //

Re: Pro tip for DELETE queries
First do the SELECT FROM bigtable WHERE <condition> bit, then when you have verified that, do a SELECT INTO bigtable_backup_todaysdate FROM bigtable (without the WHERE clause) before changing it to a DELETE statement. If the database has active transactions going on, you might also want to make sure that either everyone else is out of the database, or you might want to consider using a query locking hint, such as WITH READUNCOMMITTED.

You can always do a DROP TABLE bigtable_backup_todaysdate later, once you've established you didn't screw up.

This does, of course, assume you have enough space in your database for a copy of "bigtable", but to be honest, if you don't have that much headroom (or log space) in your database, you're likely to run into bigger problems. //

Re: Pro tip for DELETE queries
How's this for a pro tip: always Always ALWAYS surround your PROD statements with begin transaction / rollback transaction FIRST?

begin transaction

delete from bigtable where secretfield=1

rollback transaction

See how many records are impacted. If it's more than you think, carefully query the data and understand why.

Finally, when you are done, replace "rollback" with "commit":

begin transaction

delete from bigtable where secretfield=1

commit transaction

But even before you do this, do a SELECT with the same criteria as the DELETE and actually read some records.