• Replies 6 replies
  • Subscribers 342 subscribers
  • Views 93 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 Amber Sanders

    Note my caveat.  You may not want to actually concern yourself with primary flags in this case?  The customer facing issue is whether the currently entered email address has been set as the login username, regardless of whether either is primary.

    Now, you can quickly get into trouble, as an email address can appear on numerous records, but a login username (T_CUST_LOGIN.login) must be unique for each Login Type.

    select
	e.address as email_address, e.primary_ind as email_primary_ind,
	cl.*
from T_CUST_LOGIN as cl
	inner join T_CUSTOMER as c on c.customer_no = cl.customer_no
		and c.inactive = 1
	inner join T_EADDRESS as e on e.eaddress_no = cl.eaddress_no
		and e.address <> cl.login
where
	cl.inactive <> 'Y'
	and
	cl.login_type = 1 --Web Login
order by
	cl.last_login_dt desc
;

    This is a query for SSMS.

  • $organization in reply to Gawain Lavers

    Thank you that is pulling the information I am looking for.  

  • $organization in reply to Amber Sanders

    That query got me down to about 1000.  I'm seeing some cases of "off" logins being created by integrations (Goldstar), Guest Checkout (probably after a merge?) and quite a few are cashiers fixing a customer's typos (".con" to ".com"), but not reflecting that change in the attached login.

    In v16 you can set a Login Type to refer directly to the attached email address instead of storing a separate username...looking forward to that, even if there are a few (non-critical) bugs still.

Reply
  • $organization in reply to Amber Sanders

    That query got me down to about 1000.  I'm seeing some cases of "off" logins being created by integrations (Goldstar), Guest Checkout (probably after a merge?) and quite a few are cashiers fixing a customer's typos (".con" to ".com"), but not reflecting that change in the attached login.

    In v16 you can set a Login Type to refer directly to the attached email address instead of storing a separate username...looking forward to that, even if there are a few (non-critical) bugs still.

Children
No Data