0

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

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

0

You can try this, mate:

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
)
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
FROM
    db2.tblclients
WHERE
    db2.tblclients.email NOT IN (
        SELECT
            db1.tblclients.email
        FROM
            db1.tblclients
    );  

Proper formatting and capitalization based on your personal preference can help you read, understand, and find something in your query. Cheers!

  • thanks but it gives error when I try to write it on one line. The reason for that being is I would like to use the linux command mysql -u root -e "the whole query" – Amir No-Family May 14 '15 at 5:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.