Hero image!

Upsert in databases

January 16, 2024 - Drygast
Basics Database MS SQL MySQL SQLite

How to run a command that upserts data in a few different database engines.

What is UPSERT?

UPSERT is a database operation that combines the actions of UPDATE and INSERT. The UPSERT operation is designed to efficiently handle situations where you want to insert a new record into a database table, but if a record with the same key already exists, update the existing record instead. In other words, UPSERT is a way to ensure that a specific record is present in the database, and if it already exists, update it; otherwise, insert a new record.

There are different ways to achieve UPSERT functionality, and the specific implementation may vary depending on the database management system being used. Some databases provide a dedicated UPSERT statement or command, while others might use a combination of existing INSERT and UPDATE statements in a specific way.

In the context of UPSERT operations in a database table, a key is a crucial component for ensuring accurate and efficient updates or inserts. The key is a column or set of columns that uniquely identifies each record in the table. When performing UPSERT, the database relies on this key to determine whether a record already exists or needs to be inserted.

UPSERTs in MySQL

In MySQL, you could UPSERT a record in a table by using ON DUPLICATE KEY UPDATE clause as shown below. In this example I'm using a default test-database called "sakila".

USE sakila;

INSERT INTO actor (actor_id, first_name, last_name)
VALUES (1, 'PENELOPE', 'GUINESS') AS ins
ON DUPLICATE KEY UPDATE
first_name = ins.first_name, last_name = ins.last_name;

SELECT * FROM actor WHERE actor_id = 1;

In the above example, if a record with the specified actor_id already exists in the table, the ON DUPLICATE KEY UPDATE clause will update the first_name and last_name columns with the new values provided. If no record with the specified actor_id exists, a new record will be inserted.

UPSERTs in MSSQL

In Microsoft SQL Server, things change a bit. I generally like the ON DUPLICATE KEY UPDATE clause better, but that does not exist in MSSQL.

Instead, the UPSERT operation is often implemented using the MERGE statement. The MERGE statement allows you to perform multiple operations (INSERT, UPDATE, DELETE) in a single, atomic statement based on a specified condition.

MERGE Markets AS [Target]
USING (SELECT id = 1, [name] = 'SWEDEN') AS [Source] 
ON [Target].id = [Source].id -- Condition
WHEN MATCHED THEN -- UPDATE
	UPDATE SET [Target].[name] = [Source].[name]
WHEN NOT MATCHED THEN -- INSERT
	INSERT (id,[name]) VALUES ([Source].id,[Source].[name]);

This MERGE statement essentially checks for matching records based on the specified condition. If a match is found, it performs an update; otherwise, it inserts a new record. This is a concise way to achieve UPSERT functionality in Microsoft SQL Server.

MERGE has the advantage of being a lot more versitile and provides a lot of advanced options.

UPSERTs in SQLite

SQLite version 3.24.0 and later has introduced support for the ON CONFLICT clause for the INSERT statement, providing a straightforward way to achieve UPSERT functionality.

INSERT INTO Categories (categoryID, categoryName) VALUES (1, 'Driving')
	ON CONFLICT (categoryID) DO UPDATE SET categoryName='Driving';

Another option is to use REPLACE.

INSERT OR REPLACE INTO Categories (categoryID, categoryName)
	VALUES (1, 'Driving');

-- In short form:
REPLACE INTO Categories (categoryID, categoryName)
	VALUES (1, 'Driving');

This can also be used with multiple rows.

REPLACE INTO Categories (categoryID, categoryName)
	VALUES 
		(1, 'Driving'),
		(2, 'Walking'),
		(3, 'Jumping'),
		(4, 'Biking');

Summary

UPSERT operations are particularly useful in scenarios where you need to maintain the integrity of a dataset and want to avoid duplicating records or manually checking for the existence of a record before deciding whether to insert or update.