with implicit join :
INSERT into table1(id, list of other columns) select m.newID, list of other columns from table2, tmpIDMAP where m.id = table2.id
UPDATE table3, tmpIDMAP SET table3.parentID = m.newID where m.id = table3.parentID
Andrew Mull wrote:
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]
-- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
