MySql - UPDATE from one database to another database

Posted on 2014-08-28
Last Modified: 2014-08-28
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

Question by:mar2195
    LVL 34

    Expert Comment

    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.

    Author Comment

    Same server; different databases ... which has it's own DBNAME, DBUSER, DBPASS ; different users = both have full permissions/privileges
    LVL 34

    Accepted Solution

    Alternative syntax:
    UPDATE db1.users INNER JOIN db2.users ON (db1.users.emails = db2.users.emails) SET db1.users.pwds = db2.users.pwds

    Author Comment

    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?
    LVL 34

    Expert Comment

    Yes, I would ensure they both have explicitly-defined privileges on both DBs.

    Author Comment

    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

    Author Closing Comment

    Thanks so much.  I was just beginning the INNER JOIN testing.  You saved me a bunch of time.  Thanks.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now