Thank you. I'll have to review the SQL carefully as I haven't used inner joins in quite some time :)
Quoting [EMAIL PROTECTED]: > Create a temporary table that will act as a map between your old IDs and > your new ones. That way you can re-use those values and change them from > the old values to the new values. > > LOCK TABLE table1 WRITE, table2 WRITE, table3 WRITE; > > select @max_id:=max(id) FROM table1; > > CREATE TEMPORARY TABLE tmpIDMAP > SELECT id, [EMAIL PROTECTED] as newID > FROM table2 > > INSERT into table1(id, list of other columns) > select m.newID, list of other columns from table2 > INNER JOIN tmpIDMAP m > ON m.id = table2.id > > UPDATE table3 > INNER JOIN tmpIDMAP m > ON m.id = table3.parentID > SET table3.parentID = m.newID > > (repeat for other child tables) > > UNLOCK > > I don't have time to give a better explanation right now but if you write > the list back, I can fill in the details later. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > Andrew Mull <[EMAIL PROTECTED]> wrote on 12/21/2004 12:20:57 > PM: > > > You are correct in that I need to merge two tables. However, I'm > > not clear on > > how to update the FKs in the sub tables. > > > > Example, the main table (Person) has a sub table (Address). Person has > a 1:N > > relationship with Address. So Address has the Person's ID as a FK. How > do I > > update the FK in Address with the new ID assigned to Person that was > created > > with the merge? > > > > Thanks for the help! > > > > > > Quoting Philippe Poelvoorde <[EMAIL PROTECTED]>: > > > > > Andrew Mull wrote: > > > > > > > I'm working on a rather large database with many cross-linked tables > > > > > currently using auto increment IDs. The system is primarily a web > based > > > > system, however, there will be times that the system will be run as > a > > stand > > > > > > > alone server...meaning no internet connection is available. > > > > > > > > The question arises that if someone enters information to the > database on > > > the > > > > website, while others are entering information on the local > database, what > > > is > > > > the best way to merge the data? I would imagine that we would run > into > > > many > > > > duplicate auto increment IDs. > > > > > > > > I'm sure that for one table insert, this would not be a problem as I > could > > > > > > > store the SQL statement in a text file without the ID specified, and > run > > it > > > as > > > > a batch process on the live server when we get connectivity. But I > don't > > > have > > > > a handle on how to update the sub tables that have a FK pointer. > > > > > > > > Any ideas? > > > > > > > > Thanks! > > > > -Andy > > > > > > > > > > I understand your question as the following: you want to merge 2 > tables > > > comming from different database in a single table. If this is not what > > > > you want, sorry :) > > > I would do that : > > > LOCK TABLE table1 WRITE, table2 WRITE > > > select @max_id:=max(id) FROM table1; > > > UPDATE table2 SET [EMAIL PROTECTED]; > > > insert into table1(list of columns) select [list of columns] from > table2 > > > UNLOCK > > > > > > and then updating your FK within the update query. > > > Keep in mind that I didn't try with InnoDb tables... (but works fine > for > > > MyIsam) > > > > > > -- > > > Philippe Poelvoorde > > > COS Trading Ltd. > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
