I have two identical tables that are located in two identical databases(with different name). I want to merge these two tables, but their primary keys are used in other tables,
these tables look like this:
id column1 column2 column3 ___ ________ _______ ________ 1 text text text 2 text text text 3 text text text
id column1 column2 column3 ___ ________ _______ ________ 2 text text text 3 text text text 4 text text text
tables that are linked to Table A
id column1 tableA_ID ___ ________ _______ 1 text 2 2 text 3 3 text 4
id column1 tableA_ID ___ ________ _______ 1 text 3 2 text 3 3 text 2
Please note, the tables have identical
id‘s, this means when I do the merge, I have to change the
id‘s of the second table. Remember the second table’s primary keys are used in other tables.
I wrote this query to merge the two tables:
INSERT INTO db_A.`Table_A`(`column2`,`column3`) SELECT `column2`,`column3` FROM db_B.`Table_B`;
This query will correctly copy the records of the second table to the first table.
Now I want to also move the data of the tables that are linked with
Table B, I can use the same query, but now the foreign key will not match, because the
ID they were linked with has been changed.
How do I update them so that the
ID will match again?
NB: I do not have the ON UPDATE CASCADE constraint on those tables
I hope this make sense, I will try to improve this question so that everyone understands it.
Type : MySQL Engine: MyISAM
A simple way would be to update the
IDs to unique range and then do the merge. If your foreign keys are properly set to cascade the change, your database will stay consistent through this operation.
You don’t need to do any changes to the database schema this way, so there is no point in time when the data is not calid. You can also be sure that the IDs won’t clash. Easiest way to find unique values is to take the maximum of the
TableA and add that to the