We are attempting to cleanup the T_PHONE table in our database. We converted all customer data from our old system and our previous system was not as clean and clear cut as Tessitura, so there is a lot of issues with customers who have a phone 2 on the primary address but no phone 1. We want everyone to have a phone 1 on their primary address so we can mail out information for subscribers and donors to lookup their TNEW login information if they haven't already activated their login.
We are attempting to run a series of update queries that will copy phone type 2 to phone type 1 where phone type 1 is null. We then wanted to delete phone type 2 where phone type 2=phone type 1. But it is a lot more complicated than we hoped since phone and type are 2 separate columns in the table. We have tried several queries that weren't quite right and we had to request a live to test copy after every failed attempt so we were updating the same data on each test. Does anyone have any better approaches to projects like this, or is there a built in utility in Tessitura that can do this stuff for us that we are just not finding?
Unknown said: [...] there is a lot of issues with customers who have a phone 2 on the primary address but no phone 1. [...]
[...] there is a lot of issues with customers who have a phone 2 on the primary address but no phone 1. [...]
If these folks truly have a type2 but no type1, and you want them to be type 1's, couldn't you just update them to type 1? Why delete anything?
Well for most of these instances, there is already a row in the table for the type 1 phone assigned to that address_no but with a null phone. Are you suggesting, we just delete out all rows in the table with a NULL phone value and update phone type 2 to type 1, where phone type 1 doesn't exist?
Hi Jesse
Yep. That's what I'd do.
Addresses in our db with a Phone 2 and no phone 1 generally have no Phone 1 record, rather than a NULL value record.
As far as I can see, that doesn't happen at all in normal use - where an address has a phone1 which is then removed that leaves the record existing, but leaves an empty string value behind in the phone field, rather than a NULL.
Actually, there are no T_PHONE records in our db with a NULL phone value - I suspect yours are artifacts of your import.
And if you create a new address with no phone1, the phone1 record doesn't get created. So it should be safe to delete them.
Ken
Ken is right:
Unknown said: Are you suggesting, we just delete out all rows in the table with a NULL phone value
Are you suggesting, we just delete out all rows in the table with a NULL phone value
Yes. Null phones are just cluttering up your T_PHONE table.
Unknown said: and update phone type 2 to type 1, where phone type 1 doesn't exist?
and update phone type 2 to type 1, where phone type 1 doesn't exist?
Yes.