What is an upsert operation, and how do you perform it using the INSERT ... ON DUPLICATE KEY UPDATE statement in MySQL? — MySQL Interview Question #37

An upsert operation, also known as a merge operation, combines insert and update operations into a single statement. An upsert operation is used when you want to insert a row into a table if it does not exist, or perform an update on the row if it already exists.

In MySQL, you can perform an upsert operation using the ‘INSERT ... ON DUPLICATE KEY UPDATE‘ statement. This statement works by specifying the columns to be inserted, followed by the keyword ‘VALUES‘, which contains the values to be inserted. If there is a conflict with an existing row, the ‘ON DUPLICATE KEY UPDATE‘ clause is executed, updating the existing row.

Here is an example of using the ‘INSERT ... ON DUPLICATE KEY UPDATE‘ statement to perform an upsert operation in MySQL:

Suppose we have a table named ‘users‘ with columns ‘id‘, ‘name‘, and ‘email‘. We want to insert a new user record if the ‘id‘ does not exist in the table or update the ‘name‘ and ‘email‘ columns if the ‘id‘ already exists.

INSERT INTO users (id, name, email)
VALUES (1, 'John Smith', 'john.smith@example.com')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    email = VALUES(email);

In this statement, we first specify the columns to be inserted (‘id‘, ‘name‘, and ‘email‘) followed by the ‘VALUES‘ keyword, which contains the values to be inserted (‘1‘, ‘’John Smith’‘, and ‘’john.smith@example.com’‘).

If the ‘id‘ already exists in the ‘users‘ table, the ‘ON DUPLICATE KEY UPDATE‘ clause is executed, updating the ‘name‘ and ‘email‘ columns with the values specified in the ‘VALUES‘ clause. The ‘VALUES()‘ function is used to reference the values originally intended to be inserted.

Note that for this upsert operation to work, the ‘id‘ column must be defined as a unique key or primary key in the ‘users‘ table.