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

[bepai]

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

[blacknight]

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

[bepai]

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.