Why hasn't SQL Server made a WHERE clause mandatory by default?

Question

It seems like a no brainer to me. I've heard countless stories about people forgetting the WHERE clause in an UPDATE or DELETE and trashing an entire table. I know that careless people shouldn't be issuing queries directly and all that... and that there are legitimate cases where you want to affect all rows, but wouldn't it make sense to have an option on by default that requires such queries to be written like:

UPDATE MyTable SET MyColumn = 0 WHERE *

Or without changing the language,

UPDATE MyTable SET MyColumn = 0 WHERE 1 = 1 -- tacky, I know

Answer 1

Because the spec doesn't require it, and you shouldn't be running ad hoc sql directly against production data anyway.

Answer 2

Making this mistake is a long engrained rite-of-passage for programmers. We've all made it - and royally screwed up production data - learning a lot in the process of fixing it. You only make this mistake once, so it's kind of fun watching a developer join the ranks when it happens.

Preventing it from happening would ruin all the fun!

:)

Answer 3

Joel, I think the point of Josh's question is why doesn't the spec require it, or at lease have an Option Setting that will make the specific database require it?

Since the spec does not require it as you say, there is an opportunity for an errant query (either ad hoc or simply a programatic bug) to change rows in the database you didn't intend to change.

An implicit "ALL ROWS" seems way more dangerous than an implicit "NO ROWS".

Answer 4

I know from experience that you only make this one once... after it happens once you always make sure to never let it happen again!!!

Answer 5

Just to play it safe we can always run in a transaction:

BEGIN TRAN

UPDATE MyTable SET MyColumn = 0

Then if the row count looks good:

COMMIT TRAN

Answer 6

from my point of view this is a rethoric question, I mean it's kind of a suggestion...

In deed, I find it is a really good one, there could be some setting like "SAFE_UPDATE" or something like that...

what I usually do, besides robin's tip (ALLWAYS open a transaction), is to run the query with select, just to have a look at the records that will be updated, something like this

update mytable set column = xx
-- select * from mytable
where mycondition = mycondition

before updating I just select the from the select and see what it returns...

anyway, you should allways have a backup (I heard sql 2005 snapshots are pretty cool too) and work inside a transaction...

Answer 7

Isn't it possible to set auto commit to false in your client session as a default? You have to issue a "commit" to see your changes that way, in an almost "are you sure you want to do this?" fashion.

I believe that was the default for all Oracle TOAD clients at a former employer.

Answer 8

Don't try to make your system idiot-proof - idiots are extremely ingenious! :-)

What would stop them to just simply add WHERE 1=1 to every query - possibly even from a template? I don't think this would accomplish anything at all....

Marc

Answer 9

Josh it actually is not all that unusual to want to delete or update all rows in a table.

Answer 10

MySQL DOES provide that option for ad hoc queries. It's called --safe-updates (or --i-am-a-dummy). As others have pointed out, we've all made that mistake once. Those of us who run ad hoc queries all the time, sometimes at 1AM, have made the mistake more than once.

Although I normally hate "idiot proof" systems and "are you sure" dialogs, I like this option. You should always be careful, but even being careful you'll make perhaps one error per thousand hours. If you spend 50 hours a week logged in as root on production systems, one error per thousand hours is 2 1/2 major screw ups per year. For that reason, and one other, we find --safe-updates to be very useful.

There are two reasons that it's more useful than most MS confirmation messages. First it catches something that is most likely to be an error, unlike "are you sure you want to delete that file?". Most file deletes really are desired, so the confirmation is an annoyance. Most "delete from users" , if missing a where clause, really is an error. Secondly, it points out exactly what the likely problem is - the missing where clause. It's as if the file delete confirmation was smart enough to say "that's the brand new updated copy, not the old copy you think you're deleting. Do you really want to delete the new copy, or did you intend to delete the old one instead?"

Anyway, generally I hate "idiot proof", but I like --safe-updates, and if it's an option those who don't want it need not use it. My one concern is that if you constantly work on a system that DOES have the feature, someone might get sloppy and be in trouble when they switch to a system without it, such as switching from MySQL to MSSQL.

Scratch the last caveat - no one in their right mind would switch to MS after becoming familiar with open source. :)