Jump to content

Update table...where remote db table...


Recommended Posts

Right, I seem to be missing something vital regarding sql...


I feel like there should be a selection of data but I've got to the point here I need a massive push off this cliff of confusion... using MySQL 5.0.99


UPDATE table1 SET member_id = 'localdb1.members.member_id' where usernamex ='localdb1.members.members_old_name'


I know I'm doing this wrong but I do not know what to search for to get me going again. What I want to achieve is update a federated table by populating an empty field for every row with the values of a different field in a different database and table where the names match.


FederatedDB: usernamex,member_id = newuser99,0

LocalDB: member_old_name,member_id= newuser99, 29301


I wish to place member_id 29301 into the federatedDB for all entries.


What am I doing wrong, should I be using insert into... or what...

Link to comment
Share on other sites

asuming you have connected to both db's and tables try..

t1 external

t2 local


UPDATE table1 t1, (SELECT member_old_name, member_id FROM t2) AS t2 SET t1.member_id=t2.member_id WHERE t1.usernamex = t2.member_old_name;


let me know how you make out..

Link to comment
Share on other sites

That worked perfectly, thank you =)


I see now that I need to group up the select, I just couldnt get it round my head how to "store" the values before inserting. Thank you.


Perhaps you could also shed some light on a trigger, though I'll make a new topic for that.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.