• Adam Thurgar

Update without a WHERE


It was a normal work day. Then all of a sudden, nobody could login to the web sites. This affected thousands of customers. There was an air of anxiety as everybody tried to figure out what had happened. It turned out that the mid level DBA had done a password update for a customer, but forgot the WHERE clause in the UPDATE statement. I am pretty sure that we have all done this either with an update or a delete. We were now in a situation where we needed to recover and quickly. We restored the last full backup to another server and applied the transaction logs to the time just before the update. This gave us the data that we needed to recover so we could then do a mass update. I had to take some of the blame. I should have checked the update before it was run, it should have been in an explicit transaction - where you could check the row count before committing. It also made us look at our processes. Direct access was severely restricted. Data changes need to be done through stored procedures., through an admin web site. Questions raised about whether we should dump out the contents of tables before any changes were made. It ended up being anything but a normal day and nobody got fired, we all make mistakes.


7 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t