mysql - 将行从 srce 复制到 dest 忽略 dest 中已有的记录

Question

我在两个不同的数据库中有两个表，其结构略有不同:destTable:姓名、地址、城市、州、邮政编码、国家/地区、电子邮件、电话、公司srceTable:公司、地址、城市、州、邮政编码、国家/地区、姓名、电子邮件、电话

当我尝试使用此语句时:

INSERT INTO db1.destTable (name, address, city, state, zip, country, email, phone, company) 
SELECT company, address, city, state, zip, country, name, email, phone  
FROM db2.srceTable 
WHERE db2.srceTable.email NOT EXISTS(SELECT email  
                 FROM db1.destTable  
                 WHERE (db2.srceTable.email=db1.destTable.email)

我收到此错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS(SELECT email FROM srceTable' at line 4

我尝试了几个版本，并将 EXISTS 替换为 NOT IN，但一切都会产生错误。

这个副本的 secret 是什么？(哦，我只有电子邮件字段，我担心它是重复的)。

谢谢

皮特

Answer 1

您缺少 NOT EXISTS 语句中的最后一个 )。

INSERT INTO db1.destTable (name, address, city, state, zip, country, email, phone, company) 
SELECT company, address, city, state, zip, country, name, email, phone  
FROM db2.srceTable 
WHERE db2.srceTable.email NOT EXISTS(SELECT email  
                 FROM db1.destTable  
                 WHERE (db2.srceTable.email=db1.destTable.email))