SQL Fixins
First watch, 4 bells (10:11 pm)

Today I discovered (somewhat belatedly) that there was a problem with our CRM system. Apparently about a month ago some information (over 93,000 records) had their country information set to NULL. Chances are real good that it was someone running a query and accidentally did it, but it's difficult to pin down when it happened so long ago. I don't even have the day it started, and nobody came and told me until I ran across it while demonstrating how to import records and was told: "Yeah, it's been about a month since all the Country fields went blank". DOH!

I tossed several methods of recovering the data, from restoring backup tapes of a month ago and copying the information from the old tables (at least it would fix most of our problem), but I think the best solution was this: For each Account, we have a Primary Contact. Each Primary Contact also stores country information. By assuming (and we're 99.9% sure this is the case) that the Primary Contact for an Account resides in the same country as the Account is based, we can just set those fields equal to each other. This is true because each Account/Primary Contact pair was created from a single Lead that contained the original information, so unless somebody changed the Address of the Primary Contact to be in a different country, we're okay. And if someone did, I'll just blame it on them anyway 🙂

In the end, it took less than five minutes to write the SQL code to fix a problem that everyone in the office decided to "live with" instead of mentioning it to me. I know, I'm busy. But some things are easy to fix.

Leave a Comment »