The designers of SQL for some reason did not require a keyword (like “ALL”) to protect SQL statements from accidental updates or deletes. Especially when the UPDATE or DELETE statement doesn’t contain a WHERE clause. I wonder if this was deliberate or if it was an oversight. In any case, the programmer becomes “all-powerful”. The significance of this problem will be better appreciated with an example…
Let’s say you have a customer table (CUSTOMERS) with fields like ID and FNAME. Let’s further assume that this table holds one hundred thousand (100,000) records. Now, you want to write a simple query to update the first name of a customer whose customer ID is 15. You are supposed to use the following query…
UPDATE CUSTOMERS SET FNAME = "New First Name" WHERE ID = 15
Now, because you’ve had a hard day, you simply forgot to add the WHERE part. So you ended up writing this…
UPDATE CUSTOMERS SET FNAME = "New First Name"
And you executed the statement!
Immediately after executing, you realise that there was an error in your query. Sadly however, that was not a cheap error. You just lost (or messed up) a table with one hundred thousand records! All customers now have “New First Name” as their first name!! The only ray of hope would be if backups of the database had been made previously.
After making this grievous mistake more than once, I’ve religiously gotten into the habit taking the following steps whenever an UPDATE or DELETE query is needed.
- As I type in my query, 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.
- I enclose my statements within this:
BEGIN TRANSACTION; Select Blah --Some Sql here that changes Blah Select Blah ROLLBACK TRANSACTION;
Then I run it and inspect the affected rows count to see if it looks sane. When I’m sure its ok, I replace ROLLBACK with COMMIT and run it for good.