Page 1 of 1

Syncing Two Fields in Ywo Different DB's

Posted: Sat Aug 23, 2003 11:28 am
by jhesch
Hello,

I have two different mySQL databases that each have the same structure. One of the databases has some updated data that I would like to use to update the other database.

I have been unable to find any code that will take the data from one field and update the other database field if it's empty. I tried SQLog but it only syncs an entire datbase.

Anybody have any ideas? I'm not proficient enough to write my own script to accomplish this and I'm hoping there is already something like this written.

Thanks,

John

Posted: Mon Aug 25, 2003 8:45 pm
by JAM
I cant remember exactly how I did, so this is just me trying to recall...

New = The one with updated.
Old = The one with blanks.

I copied one of the so that they where on the same server (you allready have that i presume). I used something similiar to:
"update old set old.field = new.field inner join new on new.id = old.id where old.field is null"

Using a join with id's, i get a match to update. With where old.field is null i only select the empty fields.

Check the mysql's manual, and (now knowing how good/bad SQLog is) consider trying out other sql managers as phpMyAdmin or MySQL-Front.

Good luck.

Posted: Mon Aug 25, 2003 9:19 pm
by fractalvibes
If both databases reside on the same server - what is the need to replicate the data? JAM's idea would probably work with some tweaking for updating, but you would still need to handle inserts and deletes...

fv

Posted: Mon Aug 25, 2003 9:33 pm
by fractalvibes
Something to look at:

http://www.mysql.com/doc/en/Replication.html

http://www.mysql.com/doc/en/INSERT_SELECT.html

And, from the manual:

Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

and:

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID
FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;

So that sql might be something along the lines you are looking for...

fv