MySql - UPDATE from one database to another database

FYI: I have tried using other solutions that have been posted to resolve this query, but haven't had any success. I have decided to use the code below as it seems the most logical to accomplish my task. However, if there is a solution that is completely different, that is fine - as long as it works.

Within the same server, there are two databases with the same field names.

Both DBs:

I am trying to grab all passwords from DB#2 (correct pwds) and move them to DB#1 (incorrect pwds) based upon each user's email address. Unfortunately, I cannot use ID's as they are different for each user per database.

The code below is not working. I am not receiving any errors - it's just not making the update.

Also, I realize that this type of coding has been deprecated. If anyone has this solution using newer syntax, that would be great.




$conn1 = mysql_connect($dbhost1, $dbuser1, $dbpass1);
$conn2 = mysql_connect($dbhost2, $dbuser2, $dbpass2, true);
if(! $conn1 || ! $conn2 )
die('Could not connect to db1 or db2: ' . mysql_error());

mysql_select_db('db1', $conn1);
mysql_select_db('db2', $conn2);

$query = "UPDATE db1.users as t1, db2.users as t2 SET t1.pwds = t2.pwds WHERE t1.emails = t2.emails";

mysql_query($query, $conn2);



Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Are the databases on different servers? Why do you have two separate connections?

If both databases are on the same server and MySQL instance, does your user account have sufficient privileges to read and write to both databases?

Finally, I would suggest using this syntax:

UPDATE db1.users t1 SET pwds=(SELECT pwds FROM db2.users t2 WHERE t1.emails=t2.emails);

Try that syntax on a dummy set of data first, though.
mar2195Author Commented:
Same server; different databases ... which has it's own DBNAME, DBUSER, DBPASS ; different users = both have full permissions/privileges
Alternative syntax:
UPDATE db1.users INNER JOIN db2.users ON (db1.users.emails = db2.users.emails) SET db1.users.pwds = db2.users.pwds

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mar2195Author Commented:
SELECT command denied to user ....

This seems to occur for whichever DB I try to use the UPDATE.

Meaning... if I comment out either one of the " mysql_select_db('db1', $conn1); " lines of code and create a simple SELECT as a query, all is fine.  As soon as I allow for both " mysql_select_db('db1', $conn1);  mysql_select_db('db2', $conn2); " lines of code, I get the error.

As I said, I confirmed that ALL PRIVILEGES have been enabled for both users.  Should I go back and assign one of the users to both DBs.  Will that help things?
Yes, I would ensure they both have explicitly-defined privileges on both DBs.
mar2195Author Commented:
HOORAY!!!  Assigning both users to both DBs with ALL PRIVILEGES was the answer.  Got it.  Thanks.

FYI:  The 'final' syntax that I used for the query was:
UPDATE db1.users INNER JOIN db2.users ON (db1.users.emails = db2.users.emails) SET db1.users.pwds = db2.users.pwds
mar2195Author Commented:
Thanks so much.  I was just beginning the INNER JOIN testing.  You saved me a bunch of time.  Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.