CODEWITHSUNDEEP
phpmysqlmerge
David Meagor
David Meagor

Reputation: 127

Merging two MySQL tables

I need to merge any missing rows in a production mysql database from a backup database on restored to the same machine.

How can I do this whilst preserving the original primary key id's which are auto increment.

Upvotes: 1

Views: 848

Answers (2)

Sebas
Sebas

Reputation: 21522

--create a backup just in case
CREATE TABLE table_prod_sav SELECT * FROM table_prod;

--insert the missing rows
INSERT INTO table_prod (id, field) 
    SELECT b.id, b.field 
    FROM table_backup b LEFT JOIN table_prod p ON b.id = p.id
    WHERE p.id IS NULL;

--Check consistency...

--DROP TABLE table_prod_sav;

Upvotes: 1

Ben Poulson
Ben Poulson

Reputation: 3505

You can try:

INSERT IGNORE INTO table_1 SELECT * FROM table_2;

which allows those rows in table_1 to supersede those in table_2 that have a matching primary key, while still inserting rows with new primary keys.

Alternatively,

REPLACE INTO table_1 SELECT * FROM table_2;

will update those rows already in table_1 with the corresponding row from table_2, while inserting rows with new primary keys.

Upvotes: 2

Related Questions