intro
Let's find out everything you need to know about the SQL upsert operation to either add or update rows in a table.
A common scenario for avoiding duplicates in a table is to insert a record only if it is not already present. Otherwise, you need to update the existing record with new values. Normally, this would require multiple queries and/or application-level logic. However, considering how frequent that scenario is, most databases provide an SQL upsert implementation to either insert or update records in a single operation.
In this guide, you will learn about the upsert SQL operation, including how it works and when to use it.
Let's dive in!
What Is the SQL Upsert Operation?
The term "upsert" comes from the combination of the words "update" and "insert." So, the SQL upsert operation merges the functionality of both INSERT and UPDATE commands. Specifically, it inserts a new record into a table if it does not already exist or updates the existing record if it does.
Typically, the database determines whether a record exists based on unique indexes or conflict conditions specified in the upsert query. Thus, when performing an upsert in SQL, the database inserts a new row if the record does not already exist based on some conflict conditions, or it updates the existing record with the new values if a conflict is detected.
Use Cases of the SQL Upsert Command
The upsert SQL operation helps maintain data integrity by preventing duplicate records in a table. This operation is particularly useful in three scenarios:
As you can imagine, the applications of the SQL upsert statement are broad, ranging from inventory management to order processing.
Upsert SQL Syntax
Time to explore how to use the upsert operation in different SQL dialects!
From now on, let's assume we have a users table with an auto-incrementing id as the primary key and a username column with a UNIQUE constraint. The table contains a row where the username is 'maria_williams' and the email is 'maria.williams@example.com' :
MySQL
MySQL allows you to implement the SQL upsert operation through the INSERT ... ON DUPLICATE KEY UPDATE statement.
If the ON DUPLICATE KEY UPDATE clause is specified in an INSERT statement and the value is a duplicate, your database will update the existing row.
Use the INSERT ... ON DUPLICATE KEY UPDATE statement as shown below:
1
INSERT INTO users (username, email)
2
VALUES ('maria_williams', 'maria.williams_2@example.com')
3
ON DUPLICATE KEY UPDATE email = 'maria.williams_2@example.com';
Equivalently, if you want to avoid repeating the email, you can write:
1
INSERT INTO users (username, email)
2
VALUES ('maria_williams', 'maria.williams_2@example.com')
3
ON DUPLICATE KEY UPDATE email = VALUES(email);
Now, if no row with username = 'maria_williams' exists, the following INSERT query will be executed:
1
INSERT INTO users (username, email)
2
VALUES ('maria_williams', 'maria.williams_2@example.com');
If a row with username = 'maria_williams' already exists, this UPDATE query will be run:
1
UPDATE users
2
SET email = 'maria.williams_2@example.com'
3
WHERE username = 'maria_williams';
Thus, if maria_williams already exists in the database, the statement updates the email field to 'maria.williams_2@example.com'. Otherwise, it adds a new row.
If the email column had a UNIQUE constraint, and a condition like username = 'maria_williams' OR email = 'maria.williams_2@example.com' matched several rows, only one row would be updated. In general, it is a good practice to avoid using ON DUPLICATE KEY UPDATE on tables with multiple unique indexes, as that can lead to unexpected behavior by the database.
Keep in mind that ON DUPLICATE KEY UPDATE clause can contain multiple column assignments separated by commas.
Note that the same approach is also supported by MariaDB. Learn more about the two database management systems in our comparison guide on MariaDB vs MySQL.
PostgreSQL
PostgreSQL supports the upsert SQL operation via the INSERT ... ON CONFLICT statement. Use it as in the following example:
1
INSERT INTO users (username, email)
2
VALUES ('maria_williams', 'maria.williams_2@example.com')
3
ON CONFLICT
4
DO UPDATE SET email = 'maria.williams_2@example.com';
Or, equivalently:
1
INSERT INTO users (username, email)
2
VALUES ('maria_williams', 'maria.williams_2@example.com')
3
ON CONFLICT
4
DO UPDATE SET email = EXCLUDED.email;
If the specified username already exists, PostgreSQL will update the email field to the new value. The EXCLUDED keyword refers to the values proposed for insertion.
Note that ON CONFLICT lets you specify an optional alternative action to raise a unique violation or exclusion constraint violation error. In the following example, the conflict occurs on duplicate emails:
1
INSERT INTO users (username, email)
2
VALUES ('john_doe', 'john.doe@example.com')
3
ON CONFLICT (email)
4
DO UPDATE SET username = EXCLUDED.username, email = EXCLUDED.email;
In this case, the conflict happens if the email column has a duplicate value. In that scenario, PostgreSQL will update the username and email fields of the row with the conflicting email with the new values read using the EXCLUDED syntax. As you can see, the DO UPDATE SET clause can include a list of columns separated by commas.
To execute the ON CONFLICT DO UPDATE queries, you need both the INSERT and UPDATE privileges on the columns that are listed on the statement.
Find out more about this approach in our dedicated PostgreSQL upsert guide.
SQL Server
Unfortunately, SQL Server does not provide a direct upsert statement. However, that does not mean you cannot achieve an SQL Server upsert mechanism with some custom logic!
A common way to implement upsert in SQL Server is to use the MERGE statement as in this example:
1
MERGE INTO users AS target
2
USING (VALUES ('maria_williams', 'maria.williams_2@example.com')) AS source (username, email)
3
ON target.username = source.username
4
WHEN MATCHED THEN
5
UPDATE SET target.email = source.email
6
WHEN NOT MATCHED THEN
7
INSERT (username, email)
8
VALUES (source.username, source.email);
The MERGE statement merges the source and target tables. In detail, if the username already exists in the users table (the target), the email field is updated. If no match is found, a new row is inserted with the given username and email values.
Another way to implement upsert is by using the system function @@ROWCOUNT to check whether an update happened. If it did not happen, you can use an INSERT as follows:
1
UPDATE users
2
SET email = 'maria.williams_2@example.com'
3
WHERE username = 'maria_williams';
4
5
IF @@ROWCOUNT = 0
6
BEGIN
7
INSERT INTO users (username, email)
8
VALUES ('maria_williams', 'maria.williams_2@example.com');
9
END;
This SQL script attempts to update the existing row with the username = 'maria_williams'. @@ROWCOUNT returns the number of rows affected by the last statement. So, if @@ROWCOUNT equals 0, no rows were updated. That means the maria_williams row does not exist, and the INSERT statement is executed accordingly.
There are other possible approaches to implement the upsert SQL Server operation, but the two methods above are the most commonly used.
Oracle
Similar to SQL Server, in Oracle, you can implement upsert using the MERGE statement:
1
MERGE INTO users target
2
USING (SELECT 'maria_williams' AS username, 'maria.williams_2@example.com' AS email FROM dual) source
3
ON (target.username = source.username)
4
WHEN MATCHED THEN
5
UPDATE SET target.email = source.email
6
WHEN NOT MATCHED THEN
7
INSERT (username, email)
8
VALUES (source.username, source.email);
1
BEGIN
2
INSERT INTO users (username, email)
3
VALUES ('maria_williams', 'maria.williams_2@example.com');
4
EXCEPTION
5
WHEN DUP_VAL_ON_INDEX THEN
6
UPDATE users
7
SET email = 'maria.williams_2@example.com'
8
WHERE username = 'maria_williams';
9
END;
Advanced Data Management with DbVisualizer
Upsert is definitely a useful operation for managing data, but it is nothing compared to the capabilities offered by a feature-rich tool like DbVisualizer.
DbVisualizer is a top-ranked database client with a range of advanced features, including in-line data editing. This feature allows you to visually insert or edit data directly within tables, saving significant time and effort in data management:
Explore all DbVisualizer’s advanced features and take advantage of a 21-day free trial of the Pro version. Enhance your data management experience!
Conclusion
In this article, you saw what the SQL upsert operation is, how it works, and how to use it in MySQL, PostgreSQL, SQL Server, and Oracle. With this knowledge, you are now equipped to handle conditional inserts in SQL.
Upsert is a powerful data management feature that becomes easier to handle with a robust database client like DbVisualizer. DbVisualizer supports numerous DBMSs and offers in-line data editing, advanced query optimization, and drag-and-drop query construction. Download DbVisualizer for free now!
FAQ
Is upsert part of the SQL standard?
No, the upsert operation is not part of the SQL ISO/ANSI standard. Still, it is widely supported across various database systems with different syntaxes. While the exact implementation varies among SQL dialects, the concept of combining insert and update functionality is available in most databases.
When should you use upsert in SQL?
The use of upsert in SQL is recommended in situations where you need to insert a new record into a table or update an existing record in case of a conflict. That helps maintain data integrity and avoid duplicate rows.
Is there a direct implementation of upsert in SQL Server?
No, unfortunately, there is not a direct implementation of upsert in SQL Server. However, you can implement SQL Server upsert operations by using MERGE statements or a combination of INSERT and UPDATE with @@ROWCOUNT.
Is the upsert SQL operation an anti-pattern?
Many consider the upsert SQL operation an anti-pattern because it can lead to performance issues, such as the database having to locate the record that needs to be updated twice, particularly in some SQL Server implementations. To learn more about avoiding these drawbacks, refer to the dedicated guide by Aaron Bertrand.
Is UPSERT part of the SQL commands like INSERT or UPDATE?
No, UPSERT is not a standard SQL command like INSERT or UPDATE. Instead, it is a concept implemented using different syntaxes across various databases. Explore our article to discover all SQL commands.
