MySQL UPSERT

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.

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 PRIMARY KEY column instead of raising error, it just ignores the duplicate insert. It only inserts new unique values and does not insert for duplicate entry. It is very useful for bulk insert operation.
2. Using the ON DUPLICATE KEY UPDATE Clause – This clause inserts new value for unique insert or update the value for duplicate insert.
3. Using the REPLACE Statement- 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 basically 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.

MySQL

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', 'john@example.com');

Output:

Query OK, 1 row affected

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', 'john@example.com')
ON DUPLICATE KEY UPDATE email = 'john@example.com';

Output:

Query OK, 0 rows affected

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 ‘john@example.com‘.

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', 'john@example.com');

Output:

Query OK, 2 rows affected

REPLACE Statement Output

Expalantion:

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.