copy rows from srce to dest ignoring records already in dest

I have two tables in two seperate databases with slightly different structures: destTable: name, address, city, state, zip, country, email, phone, company srceTable: company, address, city, state, zip, country, name, email, phone

When I try to use this statement:

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)

I get this error:

#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

I've tried several versions of this and replaced EXISTS with NOT IN and everything produces an error.

What's the secret to this copy? (oh, and I only have the email field开发者_运维知识库 that I worry about being a dup).

Thanks,

Pete

You're missing the last ) from the NOT EXISTS statement.

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))

You could just make a unique index on the email column.

Then do INSERT IGNORE INTO ...

The records already in the destination will be silently ignored. No need for the where clause.

you have to remove db2.srceTable.email after WHERE clause and add an ) at the end, also reorder your SELECT fields, you can have mixed data in your columns else

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

Maybe you were trying to use NOT IN?

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

Also the number of columns in your INSERT and SELECT statements were different. EDIT: OK, so the fields were subtly different in that I failed to notice the name in the SELECT statement. I've edited my statement so now it swaps the name and company, because I still can't understand the ordering in the original statement.