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]
