UPSERT - (MySQL Misc)

UPSERT is a combination of the words "UPDATE" and "INSERT". It refers to a database operation that inserts a row into a table if the row does not exist, or updates the row if it already exists. In MySQL, the UPSERT operation can be performed with the help of the INSERT ... ON DUPLICATE KEY UPDATE statement.

Syntax

The syntax for UPSERT in MySQL is as follows:

INSERT INTO table_name (col1, col2, col3)
  VALUES (val1, val2, val3)
  ON DUPLICATE KEY UPDATE col1=new_val1, col2=new_val2, col3=new_val3;

Here "table_name" is the name of the table you want to insert into or update, and "col1", "col2", and "col3" are the names of the columns in the table. "val1", "val2", and "val3" are the values you want to insert into the table. If a row with the same values for column(s) marked as UNIQUE or Primary key, it will update those rows instead of inserting new rows. "new_val1", "new_val2", and "new_val3" are the new values you want to update the row(s) with.

Example

Let's say you have a table called "employees" with three columns: "id", "name", and "salary". You want to insert a new row with id=1, name="John", and salary=50000. If a row with id=1 already exists, you want to update the name and salary columns to "John Smith" and 60000, respectively. Here's how you can do it with UPSERT:

INSERT INTO employees (id, name, salary)
  VALUES (1, 'John', 50000)
  ON DUPLICATE KEY UPDATE name='John Smith', salary=60000;

If a row with id=1 already exists, it will update the "name" and "salary" columns of that row to "John Smith" and 60000, respectively. If a row with id=1 does not exist, it will insert a new row with the specified values.

Explanation

In the example above, we used the INSERT ... ON DUPLICATE KEY UPDATE statement to insert a new row into the "employees" table or update an existing row if it already exists. We specified the values we want to insert into the "id", "name", and "salary" columns in the "VALUES" section of the statement. In the "ON DUPLICATE KEY UPDATE" section, we specified the columns we want to update and their new values.

If a row with the same "id" value already exists in the "employees" table, the "ON DUPLICATE KEY UPDATE" section will be executed and update the "name" and "salary" columns of that row. If a row with the same "id" value does not exist, the "INSERT" section will be executed and insert a new row with the specified values.

Use

UPSERT is useful when you want to insert a new row into a table if it does not already exist, or update an existing row if it does exist. This can save you time and effort when dealing with large databases.

Important Points

• The "ON DUPLICATE KEY UPDATE" section of the statement is optional.
• When performing UPSERT, you need at least one UNIQUE or PRIMARY KEY constraint in your table.
• You can specify multiple columns to update in the "ON DUPLICATE KEY UPDATE" section of the statement by separating them with commas.

Summary

In this tutorial, we discussed UPSERT in MySQL. We discussed the syntax for UPSERT in MySQL using the INSERT ... ON DUPLICATE KEY UPDATE statement and provided an example. We also explained the purpose and usefulness of UPSERT and discussed some important points to keep in mind when using UPSERT in MySQL.