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.

Output:

Query OK, 1 row affected

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

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

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.

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.