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