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