MYSQL更新IF另一个表中存在一个副本Inner Join [英] MYSQL Update IF a subtring exists in another table Inner Join

问题描述

我正在做的是为我们的数据库提供电子邮件清理脚本。在这样做时，我们确定了无效，断开或不再存在的域名列表。我们通过识别域名，即@符号之后的所有内容来实现。

  update url_links 
 set link_bad = 1， 
 emailCarrier ='bad-domain.com'
其中contact_email喜欢'[e-mail]'; 
  

标识提供者并设置该字段。问题是我们有数百个无效的域名（以上仅仅是一个例子）

我想做的是将'内部连接'到另一个表这被称为'emailCarriers'。我可以在PHP中写一个循环，但是如果有人在MySQL中有一个聪明的方法，我想在这里询问社区。

emailCarriers表包含所有不良的域名运营商，因此查询将引用emailCarriers表，并寻求在域名部分的子串上找到匹配（在@签名），如果它匹配，那么它将执行更新，并使用emailCarriers表中的相应域填写'bad-domain.com'。

谢谢！

解决方案

 更新url_links ul 
在ul.contact_email上加入emailCarriers ec，如concat （'％@'，ec.domain）
 set ul.link_bad = 1，
 ul.emailCarrier = ec.domain; 
  

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.

update url_links
set link_bad=1,
    emailCarrier='bad-domain.com'
where contact_email like '[e-mail]';

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.

Thanks!

解决方案

update url_links ul
join emailCarriers ec on ul.contact_email like concat('%@', ec.domain)
set ul.link_bad=1,
    ul.emailCarrier=ec.domain;

这篇关于MYSQL更新IF另一个表中存在一个副本Inner Join的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！