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

Question

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

peterm · Accepted Answer · 2015-05-14T14:42:18.237

0

Try

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

edited May 14 '15 at 14:42

answered May 14 '15 at 05:38

peterm

91,357
15
148
157

the very last ) was unnecessary so you have just saved my day thanks! – Amir Hajiha May 14 '15 at 12:38

score 0 · Answer 2 · answered May 14 '15 at 06:31

0

I've just simplyfied table structures, but the idea must be clear enough:

create table t1 (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(30),
  name VARCHAR(30));
create table t2 (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(30),
  name VARCHAR(30));

INSERT INTO t1 (email, name)
VALUES ('one@example.com', 'name1'), ('two@example.com', 'name2');

INSERT INTO t2 (email, name)
VALUES ('one@example.com', '2name1'), ('three@example.com', '2name3');

INSERT INTO t1 (email, name)
SELECT t2.email, t2.name from t2
  LEFT JOIN t1 on t1.email = t2.email where t1.id IS NULL;

SQLFiddle sample

answered May 14 '15 at 06:31

Aleksandr K.

528
2
12

can you suggest something generic ? I don't want to use one@example.com as there are too many records – Amir Hajiha May 14 '15 at 10:45
What do you mean? Where are too many records? It's a simple example: to exclude some records from a table2 which exist in table1, left join table2 and filter it by comparing to NULL. There are many examples of using such method, one is here http://stackoverflow.com/a/4560613/4786219 – Aleksandr K. May 14 '15 at 11:06

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

2 Answers2