How to Update Login Username to Match Their Emails

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.

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.

thanks

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.

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

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.

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.