Does MySQL Offer a MERGE Statement Equivalent?

Can MySQL Utilize the MERGE Statement?

Incorporating both INSERT and UPDATE operations into a single query is often desirable. Many SQL databases offer a MERGE statement for this purpose. However, does MySQL support such a statement?

MySQL and the MERGE Statement

MySQL does not explicitly support the MERGE statement. However, it does provide an alternative that achieves similar functionality: INSERT...ON DUPLICATE KEY UPDATE.

Syntax and Usage

The INSERT...ON DUPLICATE KEY UPDATE statement allows you to insert a new row into a table, and if a row with the same primary or unique key already exists, it will update the existing row with the new values.

The syntax is as follows:

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

Example

To illustrate its usage, consider the following example:

INSERT INTO Employees (id, name, salary)
VALUES (1, 'John Doe', 50000)
ON DUPLICATE KEY UPDATE
  salary = salary + 10000;

If an employee with the ID 1 already exists in the Employees table, this statement will update their salary by adding 10000. Otherwise, it will insert a new row for John Doe with a salary of 50000.

The above is the detailed content of Does MySQL Offer a MERGE Statement Equivalent?. For more information, please follow other related articles on the PHP Chinese website!