SQL is Designed to Ruin Your Day

Posted Sun Nov 13 @ 04:31:04 PM PDT 2011

I had my first run in with the notorious UPDATE-query-without-a-WHERE-clause catastrophe a few weeks ago. I'm surprised that in the ~5 years I've been writing SQL queries, it has never happened to me.

If you don't know what I'm talking about, here's the scoop: When you write an UPDATE query and you forget to specify the WHERE clause, every row in the table is updated. For example, this query will change Bob's email to 'bob@example.com':

UPDATE User SET Email = 'bob@example.com' WHERE Name = 'Bob'

However, if you get hasty, and forget to put in the WHERE clause:

UPDATE User SET Email = 'bob@example.com'

everyone's email gets set to 'bob@example.com'. That's definitely not something you want to do!

In my case, I wasn't querying the production database directly, so all I had to do was DROP the table and import it from a SQL dump. No big deal.

But it got me thinking. It seems like the better way to handle an UPDATE or DELETE query without a WHERE clause is to throw an error. How many cases do you really want to UPDATE or DELETE every row in a table? And in those rare cases, is it really that much more work to specify a WHERE clause like 'WHERE 1=1'?

<< Home