MYSQL Update all foreign key values

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:

Table A

id     column1     column2    column3
___    ________    _______    ________
 1        text        text      text
 2        text        text      text
 3        text        text      text

Table B

id     column1     column2    column3
___    ________    _______    ________
 2        text        text      text
 3        text        text      text
 4        text        text      text

tables that are linked to Table A

Link A

id     column1     tableA_ID
___    ________    _______  
 1        text        2     
 2        text        3      
 3        text        4

Link B

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.

Database Info

Type  : MySQL
Engine: MyISAM

Answer

A simple way would be to update the TableB‘s 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 ID in TableA and add that to the IDs in TableB.

Advertisement

Answer