MySQL MERGE Alternative: INSERT ON DUPLICATE

There is no MERGE statement in MySQL, but similar functionality can be achieved using other methods.

In MySQL, you can achieve similar functionality to a MERGE statement by using the INSERT INTO … ON DUPLICATE KEY UPDATE statement. The syntax for this statement is as follows:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...

This statement is used to insert data into a table, and if there is a duplicate unique key, the corresponding column will be updated. By specifying the columns to be updated and their values in the ON DUPLICATE KEY UPDATE clause, the update operation is performed.

For instance, let’s say there is a table called employees, with three columns: id, name, and salary, where id is the primary key. If inserting a new record, and there is already a record with the same id, then update the corresponding name and salary columns using the following statement:

INSERT INTO employees (id, name, salary)
VALUES (1, 'John Doe', 5000)
ON DUPLICATE KEY UPDATE name = 'John Doe', salary = 5000;

If a record with id 1 already exists, the name and salary columns will be updated; otherwise, a new record will be inserted.

It should be noted that the MySQL INSERT INTO… ON DUPLICATE KEY UPDATE statement can only be used for tables with a unique key. If the table does not have a unique key, a similar functionality can be achieved by creating a unique index.