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?
Hi Jesse
I don't think there's anything built-in.
But since the Type is just a value in the t_Phone record, I think there's a simpler way to achieve what you want.
I would try something like this in your sql script::
Like this, maybe...(I haven't tested this, so don't take my word for it...and that first select is painfully slow, so i wouldn't run it in Live during business hours.)
SELECT p.address_no INTO #temp
FROM dbo.T_PHONE AS p
WHERE p.type IN ( 2 )
AND p.address_no NOT IN ( SELECT q.address_no FROM dbo.T_PHONE AS q WHERE q.type IN ( 1 ) )
UPDATE t_phone
SET type = 1
WHERE type = 2
AND address_no IN ( SELECT address_no FROM #temp )