Mysql – Import/Exporting certain columns of a table across two databases and excluding duplicate emails in thesql

MySQL

I have run into a problem with mysql queries:

Basically, I have two databases that both contain a table called tblclients however db1 has far more records than db2 and there are some duplicate records as well (the users that have email address in both databases are to be excluded from the operation). I intend to append db1 to db2 in a way that only certain columns get appended as long as the email address is not found.

I have done this mysql but it gives me an error. First it complains that no database is selected and even if I type use db1; before the actual statement, it generates another error:

ERROR 1064 (42000) at line 1: 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 'db1.tblclients (db1.tblclients.firstname, db1' at line 1

any though on this?

Someone told me to use mysqldump instead but I believe it could be done in a one line mysql query to be used in a bash script file.

thanks

insert into db1.tblclients (db1.tblclients.firstname,
db1.tblclients.lastname, db1.tblclients.companyname,        
db1.tblclients.email,   db1.tblclients.address1,     
db1.tblclients.address2,     db1.tblclients.city, db1.tblclients.state,    
db1.tblclients.postcode,     db1.tblclients.country,    
db1.tblclients.phonenumber, db1.tblclients.password,    
db1.tblclients.authmodule, db1.tblclients.authdata,     
db1.tblclients.status,     
db1.tblclients.pwresetkey, db1.tblclients.pwresetexpiry,     
db1.tblclients.emailoutput) values (select db2.tblclients.firstname,      
db2.tblclients.lastname, db2.tblclients.companyname,    
db2.tblclients.email,     
db2.tblclients.address1, db2.tblclients.address2, db2.tblclients.city,     
db2.tblclients.state, db2.tblclients.postcode, db2.tblclients.country,    
db2.tblclients.phonenumber, db2.tblclients.password,     
db2.tblclients.authmodule, db2.tblclients.authdata,    
db2.tblclients.status,     
db2.tblclients.pwresetkey, db2.tblclients.pwresetexpiry,      
db2.tblclients.emailoutput where not exists (select db2.tblclients.email   
where db2.tblclients.email=db1.tblclients.email));

Best Answer

Toss the db1.tblclients. in the list of columns; it is totally redundant. And a syntax error.

A suggestion: Make the SELECT a LEFT JOIN ... WHERE id IS NULL.

And, again, db2.tblclients. is redundant. (But not harmful.)

(If those suggestions are not enough, show us what you have; there may be more to fix.)

Related Question