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