Magento 2 (adobe commerce) find customers with wrong region ID in the address

find-customer-address-with-wrong-region-id.sql

select
    cae.entity_id as "Address ID",
	cae.parent_id as "Customer ID",
	c.firstname,
	c.lastname,
	c.email,
	cae.country_id,
	cae.city,
	cae.postcode,
	cae.region as "Current Address Region",
	cae.region_id as "Current Address Region ID",
	r.default_name as "Proper Region",
	r.region_id as "Proper Region ID"
from customer_address_entity as cae
left join customer_entity as c
	on cae.parent_id = c.entity_id
left join directory_country_region as r
	on cae.region = r.default_name and cae.country_id = r.country_id
where
	(cae.region_id is not null OR cae.region_id != 0 or cae.region is not null) AND # region for country is required
	cae.region in (
		select name from directory_country_region_name
	) AND
	cae.region_id != r.region_id
order by cae.country_id ASC
-- limit 100;

The fix for the region ID mismatch (customer address vs country region table)

update customer_address_entity as cae
left join directory_country_region as r
	on cae.region = r.default_name and cae.country_id = r.country_id
set
	cae.region_id = r.region_id
where
	(cae.region_id is not null OR cae.region_id != 0 or cae.region is not null) AND # region for country is required
	cae.region in (
		select name from directory_country_region_name
	) AND
	cae.region_id != r.region_id