Avoiding the ‘WHERE clause wasn’t selected’ problem

…you know the one. In SSMS it looks like this:

You meant to only update a few rows, but you weren’t paying attention to what you’d selected before hitting the Execute button. The WHERE clause got missed from what you executed, and you’ve updated every row. Oops.

Now, I totally hear you that you could’ve put this in a transaction, checked your results and then only done a COMMIT when you were sure. You could even have put that COMMIT inside an IF block that checked @@ROWCOUNT… yes, yes, yes. You could have used an SSMS add-in to let you execute the whole query that you’re currently on. You could’ve used a different tool entirely, such as Azure Data Studio. There are plenty of ways to avoid this. But I’m going to show you something that I find works for me, especially when I’m giving the script to someone else to run.

The trick is to use a sub-query, and to run my UPDATE across that. I have to use an alias, sure, but I generally do that anyway.

It looks like this:

And the trick is that this code will not run unless the whole sub-query is selected. It’s updating the alias of the sub-query, so unless you explicitly remove the WHERE clause (which I know you might do for some unbeknown reason) and forget to put it back, you can’t select just part of this by careless mouse-and-keyboard-shortcut work.

The “SELECT *, –” section makes sure that whoever is going to run this can tell what’s about to happen, including the new values that they’re looking for, confirm the number of rows, and then highlight from the UPDATE on, knowing that it’s impossible for them to miss highlighting some of it. Because it’s just way too easy to highlight ‘not quite everything’.

I get that this idea doesn’t work quite so well when CTEs are involved, and that I might need to be careful about column names if the FROM clause has multiple tables (such as by only returning the columns from the table actually being updated and anything else I need for the sake of the update). But still, it helps.

It’s a small thing. Sometimes life is made up of a bunch of small things that together make a difference.