OK, time and time again, I see this problem. Usually with a junior level SQL developer, and its an honest mistake that I even make on occasion. Right now I'm staring at a SQL table where ALL the values for ALL the rows are exactly the same, which points to an update statement that didnt have a WHERE clause specified.
Now I ask, why does SQL allow this behavior? Surely most of the time I wouldn't want to update EVERY row with the exact same values right?
...What was that from the back? Oh, performing a whole table update? OK, here's the answer for that... add a new Where clause saying “YES... I want to run this update statement on EVERY row.” How would we do that? I dont know exactly, maybe WHERE OrderId=OrderId or something. SOMETHING that requires you to think “um, I guess I want to update EVERY row...” and it'll give pause to an errant statement typed into SQL Query Analyzer.
Ok, here comes the TABLE security crowd... ok, but what if that SQL login needs to be able to update fields? yeah... its still possible to wanton destroy all the values in every row. Stored procedures you say? You're still creating band-aids for bad wounds. The wound being missing some kind of qualifier for EVERY row.
So here's my assertion, the ANSI-SQL standard for the UPDATE and DELETE statements should be updated to force the statement to acknowledge the fact that its going to update EVERY row. Not implicitly... thats just dumb. Explicity. I should have to really really enforce that I want to update or delete every single row in that table. (Isnt that what truncate table is for anyways? even if there are relations?)