How Can I Achieve the Functionality of SQL's MERGE Statement in MySQL?

MySQL's Alternative to the MERGE Statement

SQL includes a merge statement that allows users to combine INSERT and UPDATE operations into a single query. However, MySQL does not support this statement.

Achieving the Merge Effect in MySQL

Despite the lack of a dedicated MERGE statement, MySQL provides an alternative solution:

INSERT...ON DUPLICATE KEY UPDATE

This syntax allows you to insert new rows into a table while updating existing rows if the values in a UNIQUE or PRIMARY KEY index are already present. When the new row causes a duplicate value, MySQL will execute the following steps:

1. First Check: MySQL first checks if the existing row matches the incoming row based on the unique or primary key.
2. Update if True: If a match is found, MySQL updates the existing row with the new values specified in the UPDATE clause.
3. Insert if False: If no match is found, MySQL inserts the new row as expected.

Example:

INSERT INTO my_table (id, name, age)
VALUES (1, 'John Doe', 35)
ON DUPLICATE KEY UPDATE name = 'Jane Doe', age = 40;

In this example, if the row with id 1 already exists, MySQL will update the name to 'Jane Doe' and age to 40. Otherwise, it will insert a new row with the specified values.

The above is the detailed content of How Can I Achieve the Functionality of SQL's MERGE Statement in MySQL?. For more information, please follow other related articles on the PHP Chinese website!