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

For the same reason you can have a malloc without matching free in C : it's your responsibility.

Answer 2

But really the issue is how safe is your 'Live' data. Running untested scripts on a 'Live' database is asking for trouble.

Answer 3

I don't know why either.

I bloody wish they did. The few times I want an update all or delete all I can be bothered to write WHERE 1 = 1.

Of course that wouldn't have saved Manoj who wrote

DELETE FROM T2
  FROM Tbl T1, Tbl T2
 WHERE T1.TopLevelId = 'value'

Answer 4

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.

Answer 5

I have never seen a case in which all records needed to be deleted, and precious few where all should be updated. I've always considered it a design flaw.

Answer 6

I think you'll find that they assumed you would know what you were doing, and display some modicum of intelligence :-).

I know, let's play "Reductio Ad Absurdum":

• You should not be able to delete[] in C++ without an ALL keyword. What if you just wanted to delete one element of the array?
• All those iterators that people seem to love are dangerous without forcing their users to specify ALL. What if they just wanted a subset?
• Mon Dieu! What if people were to leave a WHERE clause off their SELECT? The potential for harm is truly stupendous, it could take hours to retrieve all that data, tying up a valuable DBMS connection for the duration.

Please don't be offended, I'd prefer you to take this in the spirit it's intended (light humor). I think, as professionals, we should know what we're doing. Granted, I've made some beautiful mistakes such as appending to the passwd file with:

echo "....." >/etc/passwd

instead of using ">>" which is why I test all my potentially destructive stuff pretty thoroughly nowadays.

Answer 7

Where is it written that you need to write an SQL statement that is executable without a WHERE clause? As I type them in, I type them out-of-order so that it's not a legal SQL statement until I'm done. In other words, I type out the WHERE clause, then go back in and fill in the DELETE or UPDATE portion.

And whenever I'm doing something on a non-development environment, I perform a SELECT first to ensure that I get the proper rows with my WHERE condition, and then change it to a DELETE/UPDATE.

Answer 8

As Murphy opined : If a thing can go wrong it will. "All" would be just another case.

Answer 9

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 10

Loads of IT stuff was (is) written with the user last, and ease of development first. Sql is a great example, it works but many (trivially) easy things to do are hard. Yet things that are hard - like deleteing all your data is easy. I see it as a design flaw.

Answer 11

I believe this is due to the set-theory inspired nature of the SQL data manipulation language.

Use transactions :)