What I'm working on is an email clean up script for our database. In so doing we identified a list of domains that are invalid, broken or no longer around. We do this by identifying the domain name i.e. everything after the @ sign.
where contact_email like '%@bad-domain.com';
identifies the provider and sets the field. The problem is we have hundreds of domains that are not valid (the above is just an example)
What I'd like to do is 'inner join' to another table that is called 'emailCarriers. I could write this as a loop in PHP but I wanted to ask the community here if someone had a clever way to do this in MySQL.
The emailCarriers table contains all the bad domain carriers so the query would reference the emailCarriers table and seek to find a match on the substring of the domain name portion (after the @ sign) and if its a match then it would perform the update and fill in the 'bad-domain.com' with the corresponding domain from the emailCarriers table.