Update table without where clause!

What - an update table statement without the where clause? What are you talking about – such horrendous thing should never happen! Unfortunately, the reality is that sometimes it does happen. The first thought that comes to mind in those moments of panic after you have pressed that ever helpful ctrl+E shortcut and you realize that you either forgot to write the where clause to filter only the records that you really wanted to update or you simply didn’t select the complete statement before executing it, is to STOP the execution, but oh no, unfortunately for you the results window is displaying the message “1 million records were affected”!

Another thought flashes through your mind – how could Query Analyzer/Enterprise Manager / SSMS do this – shouldn’t I get a warning of the type "hey you are trying to update this table but I don't see a where clause. Are you sure you want to do it?"? Sure, you can blame the tool for your stupidity but that’s not going to help – the tool is just that, a tool and if it does what you tell it to do then it is really doing its job.

Then, you start beating yourself up: "why o why didn't I wrap this in a transaction" - after seeing that all rows were affected I could have simply rolled back and no harm would have been done. Well, you deserve the beating!

Are you completely doomed? Not necessarily, it is not time to run yet so pull yourself together and think this through. First if the table/columns you updated contain data of critical importance it is likely that those columns are being audited – so you can look for that audit table and use that to clean up the mess you just made. If no such thing is available then your next and likely last lifeline is the backup – restore the most recent full backup as a new database, apply the subsequent differential backups and the logs up to the last log backup before you made the mess - after you do that you can safely repair most of the damage. If for whatever reason you don’t have a backup either then you are officially doomed – just run for cover.

I emphasized here the most extreme case, that of a completely missing where clause but this applies equally to the more likely case of an incorrect “where clause” that filters the wrong set of rows. The difference is the scope (the number of rows affected) and the fact that while it is very likely that you will immediately realize what you did in the case of the missing where clause in the case of an incorrect where clause you may not even realize that you messed up until a future event uncovers the mess.

So, what’s the lesson here:

• if at all possible do not run ad-hoc update queries on a production database; A production database should only be updated through thoroughly tested interfaces that implement proper checking and warning mechanisms to avoid un-intentional changes to the database.
• if you must (that should be rare) then:
  o make a copy of what you are about to change – if you have to reverse the changes this copy will save you a lot of time and frustration;
  o always wrap your statement /script on a transaction and before you commit the transaction make sure to verify the number of rows being affected. You may also want to spot check some of the rows that were updated to ensure that what happened is what you intended to happen.
  o Insert detailed comments/explanation in the query/script you executed including the server and database against which you executed it and the exact data and time you executed it and save it.
• Always keep in mind that updating a production database is no joking matter so be safe, don’t take shortcuts!