How can I merge two MySQL tables?

Managing duplicate keys (the sequel)

If you want to play God with duplicates, ON DUPLICATE KEY UPDATE is for you. Every duplicate record bows to you, like:

INSERT INTO table1 (id, data)
SELECT id, data FROM table2
ON DUPLICATE KEY UPDATE data = VALUES(data);


Like a film director setting the scene, only update columns that needs to be. Perfect for merging partial datasets.

Dealing with autoincrement horror

Tables with autoincrement primary keys can turn into a horror movie. Use this magic spell to avoid overlapping keys:

ALTER TABLE table2 AUTO_INCREMENT = your_magic_number;
INSERT INTO table1
SELECT * FROM table2;


By increasing the AUTO_INCREMENT value, you can ensure each record keeps its identity.

Saving your data with temp tables

Using a temporary table can provide you an extra life in this data game. It can preserve integrity while you employ some moves:

CREATE TEMPORARY TABLE temp_table AS SELECT * FROM table2;
-- Insert "epic fight scene" here with transformations
INSERT INTO table1
SELECT * FROM temp_table;
DROP TEMPORARY TABLE temp_table;


This ensures you start each level with full health and a chance to revert back.

Exorcising duplicates

Before going into a merge, exorcise duplicates to keep data integrity:

DELETE t1 FROM table1 t1
JOIN table1 t2 
WHERE t1.id < t2.id AND t1.duplicate_field = t2.duplicate_field;


It's like a game tutorial kicking out redundant enemies to give you a clean path.

Merging custom columns

Sometimes, you need to perform a surgical operation to merge specific columns and avoid key duplication:

INSERT INTO table1 (id, column1, column2)
SELECT id, column1, column2 FROM table2
WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE table1.id = table2.id);


With this, you can exactly pick and place columns while avoiding duplicate key issues.

Renaming tables after merge

After a merge, renaming tables is like renaming your Pokémon for clarity:

RENAME TABLE table1 TO main_table, table2 TO archive_table;


This organizes your data like well-tamed Pokémon creatures.

Using unions for peaceful merges

If you're working for World Peace, consider using UNION:

SELECT * FROM table1
UNION
SELECT * FROM table2;


It ensures peace by strictly adhering to unique treaties(language of data) between countries(tables).

Table locks for consistent data

Physical locks for doors, table locks for data. Ensure data consistency by using LOCK TABLES:

LOCK TABLES table1 WRITE, table2 READ;
-- Epic quest starts here
UNLOCK TABLES;


Locking tables is like "Game paused", it prevents unnecessary interference during your game(merge).