• Replies 6 replies
  • Subscribers 342 subscribers
  • Views 91 views
  • Users 0 members are here
Options
Related

How to Update Login Username to Match Their Emails

Amber Sanders
$organization

For SQL Gurus out there, I am trying to figure out how to pull all of constituents whose Username does not match their Primary Email Address.  Patrons with unique usernames are having difficulties logging into the web logins on our custom website. How would I write a SQL Query to pull which patrons have none email login usernames?  How would I do a mass update to their accounts to match their emails to their usernames? 

Parents
  • $organization

    I have this as a manual query in a list we use for data integrity:

    select distinct vc.customer_no
from V_CUSTOMER_WITH_HOUSEHOLD as vc
	inner join T_CUST_LOGIN as cl on cl.customer_no = vc.customer_no
		and cl.primary_ind = 'Y'
		and cl.login_type = 1 --web login
	inner join T_EADDRESS as e on e.customer_no = vc.customer_no
		and e.primary_ind = 'Y'
where
	ISNULL(vc.inactive, 1) = 1
	and cl.login <> e.address
;

    3000 rows!  We haven't been keeping up with this I see....

    Edit: Hmmm, not this isn't detecting whether an email is out of sync with the connected login, it's just looking at the defined primary entry in each case.  And due to...behavior...in TNEW, that is actually likely to bring back a large number of responses.

  • $organization in reply to Gawain Lavers

    I do a fortnightly check of this and a few other e-mail related cleanliness items and send it off to our box office.  Initial lists were long, but it definitely cut down on the number of issues about which our customers complained, so our box office does not really complain about the regular maintenance any more.

Reply Children
No Data