How to Merge MySQL Tables with Identical Structures and Handle Primary Key Conflicts?

MySQL Table Merge: The Complete Guide

Merge two MySQL tables with the same structure, which poses unique challenges due to possible primary key conflicts. However, there are some effective ways to accomplish this task.

Method 1: INSERT IGNORE

To preserve existing rows in table_1 and only insert new rows in table_2, you can use the following query:

<code class="language-sql">INSERT IGNORE
  INTO table_1
SELECT *
  FROM table_2;</code>

Method 2: REPLACE

Alternatively, if you want to replace existing rows in table_1 with corresponding rows in table_2 while inserting new rows, you can use the following query:

<code class="language-sql">REPLACE
   INTO table_1
 SELECT *
   FROM table_2;</code>

Notes on primary key conflicts

The INSERT IGNORE and REPLACE methods handle primary key conflicts differently. INSERT IGNORE inserts a new row without conflicting primary key values. REPLACE updates existing rows regardless of primary key conflicts.

Example

Consider two tables, table_1 and table_2, which contain the following data:

table_1
id	name
1	John
2	Mary

table_2
id	name
1	Mike
2	Sarah
3	Peter

Use INSERT IGNORE to query, the results are as follows:

table_1
id	name
1	John
2	Mary
3	Peter

Rows in table_2 with matching primary keys will be ignored and only rows with non-existent primary keys will be inserted.

Use REPLACE query, the results are as follows:

table_1
id	name
1	Mike
2	Sarah
3	Peter

Rows in table_1 with matching primary keys will be replaced by corresponding rows in table_2.

The above is the detailed content of How to Merge MySQL Tables with Identical Structures and Handle Primary Key Conflicts?. For more information, please follow other related articles on the PHP Chinese website!