MySQL UPSERT is a combination of “INSERT” and “UPDATE” operations. It is used in database management to handle scenarios where you need to either insert a new record or update an existing one in a table.

This article explores the syntax, methods, and practical examples of UPSERT in MySQL, shedding light on how developers can efficiently manage data without the need for intricate conditional checks.

UPSERT IN MySQL

UPSERT in MySQL is the combination of UPDATE and INSERT operation. UPPSERT is formed from two words – “UP” from UPDATE and “SERT” from INSERT.

To use the UPSERT operation, ensure the table contains a PRIMARY KEY or UNIQUE CONSTRAINT on a column that defines the uniqueness of the record. It simplifies data management by allowing developers to handle both insertion and updating tasks seamlessly. UPSERT ensures data integrity and efficiency in MySQL databases, offering a streamlined approach to modifying data without complex conditional logic.

Syntax:

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

Parameters:

• table_name is the name of the table.
• column1, column2, … are the columns you want to insert or update.
• VALUES (value1, value2, …) specifies the values to be inserted.
• ON DUPLICATE KEY UPDATE signals that if a duplicate key violation occurs, the specified columns should be updated.
• column1 = VALUES(column1), column2 = VALUES(column2), … dictates which columns to update with the corresponding values.

Methods of UPSERT

There are three ways of using UPSERT operation in MySQL:

1. Using INSERT IGNORE– INSERT IGNORE command is an extension of the INSERT command where if there is a duplicate insert in a PRIMARY KEY column instead of raising an error, it just ignores the duplicate insert. It only inserts new unique values and does not insert duplicate entries. It is very useful for bulk insert operations.
2. Using the ON DUPLICATE KEY UPDATE Clause – This clause inserts a new value for a unique insert or updates the value for the duplicate insert.
3. Using the REPLACE Statement- The replace statement inserts new unique values in the table, but on duplicate values, it first deletes the existing row, and then adds the new row. It is a combination of INSERT and DELETE commands.

MySQL Upsert Examples

Let’s look at some of the examples of how to implement UPSERT in MySQL.

Example 1: UPSERT Using the INSERT IGNORE Statement

So, In this example, we have created the Database as INSERT_DB and Considered a ‘users‘ table with a primary key ‘user_id‘ and columns ‘username‘ and ‘email.’ We want to insert a new user or update their email if the username already exists.

The INSERT IGNORE statement allows you to insert a new row into a table. If the row already exists, the statement is ignored, and no error is produced.

CREATE DATABASE INSERT_DB;
USE INSERT_DB;

-- Create a sample 'users' table
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100)
);

-- Insert using INSERT IGNORE
INSERT IGNORE INTO users (user_id, username, email)
VALUES (1, 'john_doe', '[email protected]');

Output:

+---------+----------+------------------+
| user_id | username | email            |
+---------+----------+------------------+
|       1 | john_doe | [email protected] |
+---------+----------+------------------+

INSERT IGNORE Statement Output

Explanation: In this example, if the ‘john_doe‘ username already exists, the statement will be ignored, and no changes will occur. If the username doesn’t exist, a new user will be inserted.

Example 2: UPSERT Using the ON DUPLICATE KEY UPDATE Clause

The ON DUPLICATE KEY UPDATE clause is used with the INSERT INTO statement to specify how to handle duplicates based on a unique key or primary key constraint. In this example, we created the database as INSERT_DB and followed the same code with different queries.

-- SQL Code 

CREATE DATABASE INSERT_DB;
USE INSERT_DB;

-- Create a sample 'users' table
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100)
);

-- Insert using ON DUPLICATE KEY UPDATE
INSERT INTO users (user_id, username, email)
VALUES (1, 'john_doe', '[email protected]')
ON DUPLICATE KEY UPDATE email = '[email protected]';

Output:

+---------+----------+------------------+
| user_id | username | email            |
+---------+----------+------------------+
|       1 | john_doe | [email protected] |
+---------+----------+------------------+

ON DUPLICATE KEY UPDATE Output

Explanation:

• If a row with user_id = 1 and username = 'john_doe' does not exist, a new row will be inserted with the provided values.
• If a conflicting row already exists, the email column of that existing row will be updated to ‘[email protected]‘.

If the ‘john_doe’ username already exists, the email will be updated. If the username doesn’t exist, a new user will be inserted.

Example 3: UPSERT Using the REPLACE Statement

The REPLACE statement first attempts to insert a new row. If a duplicate key violation occurs, it deletes the conflicting row and re-inserts the new row.

-- SQL Code 
CREATE DATABASE INSERT_DB;
USE INSERT_DB;

-- Create a sample 'users' table
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100)
);

-- Insert using REPLACE
REPLACE INTO users (user_id, username, email)
VALUES (1, 'john_doe', '[email protected]');

Output:

+---------+----------+------------------+
| user_id | username | email            |
+---------+----------+------------------+
|       1 | john_doe | [email protected] |
+---------+----------+------------------+

REPLACE Statement Output

Explanation: Similar to INSERT IGNORE and ON DUPLICATE KEY UPDATE, if the ‘john_doe‘ username already exists, the conflicting row will be replaced with the new data. If the username doesn’t exist, a new user will be inserted.

Conclusion

Overall, proficiency in UPSERT operations in MySQL equips developers with a versatile tool for efficient data management. Whether the task involves updating existing records or inserting new ones, MySQL UPSERT streamlines the process, contributing to enhanced database performance.

By learning the syntax and selecting the appropriate method, developers can guarantee the integrity of their data, elevating the overall reliability of their database operations. UPSERT emerges as a valuable feature in the MySQL toolkit, simplifying the handling of data changes in dynamic and fast-paced environments.

FAQs on MySQL UPSERT

What is UPSERT in MySQL?

UPSERT in MySQL is a combination of INSERT and UPDATE operations. It allows you to insert a new row into a table or update an existing row if a duplicate key violation occurs.

When should I use UPSERT in MySQL?

UPSERT is useful when you need to insert new records into a table or update existing ones based on whether a unique key or primary key already exists in the table.

What are the methods to perform UPSERT in MySQL?

There are three main methods:

INSERT IGNORE: Ignores the insert operation if a duplicate key exists.

ON DUPLICATE KEY UPDATE: Updates the existing row if a duplicate key exists based on specified columns.

REPLACE: Deletes the existing row if a duplicate key exists and inserts a new row with the same primary key.