Sometimes when you update Magento, it could change the required state parameters and set some obligatory states for several countries in Stores > Configuration > General. Moreover, this option can be updated by your colleagues or you.
Managing a Magento 2 store, you have some clients who already set shipping and billing addresses while making a purchase. The addresses you have on your store already include countries, but not states (regions). Besides, sometimes region is set as a text field (not a select box).
Once you update the Magento, it changes the required states, or you set them to be required for a range of countries yourself.
The states will be required even if your customer already have their addresses set with the country options.
Are there any drawbacks?
When these customers want to make a purchase again, they have difficulties checking out. They see the message about their address not being valid and have to go to their account to checkout from there. You can also get some checkout errors if you use some custom payment or shipping methods.
It is not very user-friendly since it requires more time than your customers have. They can leave without buying anything that is not what any store owner would want.
What is the solution?
We recommend deleting these addresses so when your customers want to buy something repeatedly they face no difficulties. They just enter their addresses again.
However, on the other hand, it is not very time-effective to go and delete the addresses for every separate customer. So, we have a solution for you.
Run the following SQL query:
SELECT value FROM core_config_data WHERE path LIKE 'general/region/state_required'
You will get the country codes separated by commas, like in this example:
Then, you need to convert them into country codes in quotes separated by commas:
Once you have the country codes in quotes, you have to run 3 more queries.
In the first one you need to use the country codes you got earlier:
DELETE FROM customer_address_entity WHERE country_id IN ('AU','BG','BR','CA','CH','CN','CO','EE','ES','HR','IN','IT','LT','LV','MX','PL','RO','US','UY') AND region IS NULL or region_id is NULL;
UPDATE customer_entity SET default_billing = null WHERE default_billing IS NOT NULL AND default_billing NOT IN (SELECT entity_id from customer_address_entity);
UPDATE customer_entity SET default_shipping = null WHERE default_shipping IS NOT NULL AND default_shipping NOT IN (SELECT entity_id from customer_address_entity);
These SQL queries will delete invalid customer shipping and billing addresses so you face no issues with the required states.
Check out more useful development articles in Magento 2 Development category!