We always try to wrap documentation, procedure and ceremony around the occasionally necessary direct bulk update. Test in dev. Test in staging. All good. But inevitably there's that one-off special case; it's critical, there are immediate business implications, perhaps the hair of one's biggest customer is on fire. And it's late.
So this happens, and I devise a complex, clever, surgical, just-this-once query. Focused on the correctness of the cleverness, naturally I miss the elided WHERE clause deep within the SQL of a derived table. I run the query, and then spend several reflective hours restoring from backup and writing angry SQL to make things be as they were.
Having learned my lesson, I meticulously document the incident and the recovery steps. I'll certainly never make that mistake again, but I'll be magnanimous and help the next poor fool. A few months later I'm facing the same situation. This time, thank goodness, I have a clear process well documented. There's even big red warning text. A little copying and pasting of that clever SQL, execute the query, and -- what the...?!
Like a virus, the bad query with the missing WHERE clause had worked its way into my documentation of the event -- as the good code. It was convenient to have the restoration queries ready to go, shaving a bit of time off the two hour restoration process. Never assume you're not the next poor fool, I guess.
So this happens, and I devise a complex, clever, surgical, just-this-once query. Focused on the correctness of the cleverness, naturally I miss the elided WHERE clause deep within the SQL of a derived table. I run the query, and then spend several reflective hours restoring from backup and writing angry SQL to make things be as they were.
Having learned my lesson, I meticulously document the incident and the recovery steps. I'll certainly never make that mistake again, but I'll be magnanimous and help the next poor fool. A few months later I'm facing the same situation. This time, thank goodness, I have a clear process well documented. There's even big red warning text. A little copying and pasting of that clever SQL, execute the query, and -- what the...?!
Like a virus, the bad query with the missing WHERE clause had worked its way into my documentation of the event -- as the good code. It was convenient to have the restoration queries ready to go, shaving a bit of time off the two hour restoration process. Never assume you're not the next poor fool, I guess.