Avoiding the ‘WHERE clause wasn’t selected’ problem

May 14, 2020

…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.

This Post Has 4 Comments

  1. John Berry

    Hate when that happens! I think every SQL dev or DBA has made this mistake at least once. An alternative protection mechanism is the Devart SQL Complete execution warnings feature.

    1. Rob Farley

      Yes, there are a number of tools that will warn about that. But often I have to give scripts to other people to run, and I generally can’t get them to install those tools.

  2. Victor Girling

    An alternative is to use a transaction

    BEGIN TRAN

    UPDATE …

    — Number of rows looks ok?
    — COMMIT TRAN
    — Oops
    — ROLLBACK

    1. Rob Farley

      Right, but then you end up holding the locks for longer. And if someone runs it and walks away, it can be disastrous.

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.

Search