Merging Data in MySQL

You merge data in MySQL with an INSERT statement that uses the ON DUPLICATE KEY clause or a REPLACE INTO statement. The MySQL INSERT statement with the ON DUPLICATE KEY clause is the closest to the MERGE statement in Oracle because it can validate on a natural key. While the REPLACE INTO statement also accomplishes a merge, it doesn’t use a natural key comparison. The REPLACE INTO statement performs an elimination of duplicate rows based on a unique key.

Unlike Oracle, MySQL doesn’t support externally managed tables. In lieu of external tables, MySQL lets you import sources through the LOAD DATA statement or export data with a SELECT statement and an INTO OUTFILE clause. Both import and export data processes are covered in the first of the following three subsections. The subsequent sections cover examples of using both merge approaches: the INSERT statement with the ON DUPLICATE KEY clause and the REPLACE INTO statement.