Mistaken mass deletes and updates -- design error?

Question

Why didn't the designers of SQL require a keyword (e.g. "All") for any Update or Delete statements that don't have a Where clause? Was it just an oversight on their part? They would have saved so much grief (not to mention jobs!) if they had done that!

Answer 1

After making this mistake I've religiously gotten into the habit of typing

BEGIN TRANSACTION;

-- Select Blah

-- Some Sql here that changes Blah

-- Select Blah

ROLLBACK TRANSACTION;

then running it, making sure the rows affected count looks sane, staring at the Sql some more, then replacing ROLLBACK with COMMIT and running it for good.

It only takes 15 extra seconds and saves me some heartburn, especially on complex UPDATE...FROM type queries.

And indeed, I agree, there should have been an ALL keyword or some such to prevent flub ups like this. Or, a built in option in your Sql query environment that acts like the cash registers when I'd have a finger spasm and double-punch the screens at Taco Bell: "Did you REALLY mean 99 tacos?"

Answer 2

There are so many other really easy ways to corrupt your SQL data that trying to catch any one of them is really just a waste of time. An UPDATE without a WHERE is just as bad (arguably worse). Leaving off a clause or two in a SELECT joining multiple tables could cause it to print to your console, instead of one row, a trillion.

Work on replicated slaves when possible. When you can't, make sure backups are available that won't be instantly corrupted by updates. And when typing UPDATE or DELETE queries, type slowly and carefully and always stare at it for a few seconds before you type the closing semicolon.