CODEWITHSUNDEEP
mysqlsqlmergephpmyadminmyisam
Reagan Gallant
Reagan Gallant

Reputation: 863

Merge Database with duplicate primary keys and foreign keys

We have Two Mysql Databases (Myisam) that needs to be merge. They both have the same structure.The goal is to copy all the items from the one database to the other using one query to execute and do the merge.

Scenarios are as follow:

Red lines   -   Duplicate staff with same staff_id in both databases.
Blue lines  -   Duplicate staff with different staff_id in both databases.
Black line  -   Different staff with the same staff_id in both databases.
Not shown   -   Different staff with unique staff_id

red lines can be copied as is from the one database to another but the order_items_id can be increased with 10 more than the maximum order_item_id in the 'copyTo' database.

black lines Select all duplicate staff where staff names not the same?

Not shown we can be appended as is and increase order_items_id with 10 more than maximum order_items_id in 'copyTo' database.

blue lines Do I Join on staff name?

Sql fiddle link

Any advice would be appreciated.

Upvotes: 1

Views: 1001

Answers (1)

Rick James
Rick James

Reputation: 142433

SELECT ...
    FROM db1.tbl a
    JOIN db2.tbl b  ON a.staff_name = b.staff_name  -- exists in both tables
SELECT ...
    FROM db1.tbl a 
    JOIN db2.tbl b ON a.staff_name = b.staff_name
    WHERE db1.Staff_id != db2.Staff_id -- exists in both tables staff id's not matching

SELECT ...
    FROM db1.tbl a
    LEFT JOIN db2.tbl b  ON a.staff_name = b.staff_name
    WHERE b.staff_id IS NULL    -- missing from b (exists only in a)

SELECT ...
    FROM db1.tbl b
    LEFT JOIN db2.tbl a  ON a.staff_name = b.staff_name
    WHERE a.staff_id IS NULL    -- missing from a (exists only in b)

To copy ones that are only in source to dest:

SELECT @max := MAX(staff_id) + 10 FROM db1.tbl;  -- destination
INSERT INTO db1.tbl
    SELECT @max + source.staff_id, source.name, ...
        FROM db2.tbl AS source
        LEFT JOIN db1.tbl AS dest  ON source.staff_name = dest.staff_name
        WHERE dest.staff_id IS NULL

Upvotes: 3

Related Questions