Hi there,
We've got a number of addresses on constituent records where the postal code field is populated but the city is not (mostly due to our recent implementation of True Tickets, which only requires users to enter a zip code when accepting a shared ticket if they are creating a new account). If we were hand-keying these addresses in on the front-end, the city would of course populate when the zip was entered. Does anyone have a procedure for replicating this functionality on the back end in bulk? I'd love to schedule a proc to run on a daily/weekly basis to update these addresses if possible.
Thanks!
Lauren
I don't, but the data appears to be held in TR_CITYSTATE. I observe ours has create dates of 2000 and last update dates of 1900, with the exception of "00401 Readers Digest NY". Also, ours only has entries for the US. Do other people have more up-to-date or expansive tables?
Yes, our consortium has a subscription to https://zip-codes.com/. They send us a file of US zip code changes and I update TR_CITYSTATE monthly.
Lauren Gruber, I don't have a proc that does this, but something like the following should work:
UPDATE a SET a.city=LEFT(b.city,30) --truncate the city name at 30 chars. T_ADDRESS.city is 30 characters long, but TR_CITYSTATE is 40 --select * --left here in case you want to check the addresses before updating them FROM T_ADDRESS a JOIN TR_CITYSTATE b ON LEFT(a.postal_code,5)=b.zip5 AND b.default_ind='Y' --zip codes can have multiple city names but only one default name JOIN T_CUSTOMER c ON a.customer_no=c.customer_no AND c.inactive=1 ---only update addresses on active records WHERE a.country=1 --only do this for US addresses AND LEN(ISNULL(LTRIM(RTRIM(a.city)),''))=0 AND a.inactive='N' --only do this for active addresses
That looks ugly and hard to read, so here's a screenshot from SSMS with the formatting:
I didn't test that out, so it might need some tweaking. Hopefully that sends you on the right track though. Good luck!
This is fantastic! Thank you so much Katie Lachance-Duffy!
No prob! Let us know how it goes!