How do I UPDATE a row in a table or INSERT it if it doesn't exist?
To UPDATE existing or INSERT new rows in one command, you can use UPSERT. In PostgreSQL, this is done with INSERT INTO ... ON CONFLICT ... DO UPDATE, and in MySQL, you can use INSERT INTO ... ON DUPLICATE KEY UPDATE.
PostgreSQL code:
MySQL code:
Keep in mind that these operations are based on a unique constraint like a primary key or unique index.
Digging deep: UPSERT
In many databases, you may need to update existing data or insert new data if the record doesn't exist. This is referred to as an upsert operation. This section delves into how different databases manage this crucial need.
REPLACE INTO: MySQL and SQLite
MySQL and SQLite offer a REPLACE INTO command, acting as an upsert operation. If a row with a unique key already exists, this command deletes the old row and inserts a new one.
A word of caution
While convenient, REPLACE INTO has drawbacks: It removes existing rows before inserting new, leading to possible loss of foreign key relationships and activating triggers twice.
INSERT ON DUPLICATE KEY UPDATE: MySQL
An alternative to REPLACE INTO in MySQL is the INSERT INTO ... ON DUPLICATE KEY UPDATE command. This command updates a record if a duplicate key error occurs without deletion, which navigates around the earlier mentioned issues.
INSERT OR REPLACE INTO: SQLite
For SQLite, INSERT OR REPLACE INTO is used as an upsert operation. However, it shares the same potential issues as REPLACE INTO.
UPSERT and Concurrency: Cross-database Compatibility
An important issue to note is that cross-database compatibility isn't universal for upsert operations—each SQL dialect may need a slight variation of the upsert syntax. To ensure data integrity and handle concurrency problems, always wrap upsert operations in a transaction.
UPSERT and Concurrency Issues
Updating or inserting data based on concurrent requests can be a challenge. Exclusive locks or serialized transaction levels may be necessary to prevent clashes.
Best practices for dealing with concurrency:
- Transactions should always be used to ensure operations are atomic.
- Test upsert logic under concurrent access scenarios.
- Appropriate exception handling for unique constraint violations is crucial.
Demystifying UPSERT across systems
While we have dwelled on MySQL and SQLite, it's essential to understand that not all databases provide the same syntax for upsert operations.
PostgreSQL
For PostgreSQL, an upsert operation is performed using the INSERT INTO ... ON CONFLICT ... DO UPDATE statement, allowing for complex conflict targeting and action specifications.
Delving into NoSQL
In some NoSQL databases, upsert is natively implemented. MongoDB, for example, uses the update() function with an upsert option.
SQL Server and MERGE
SQL Server uses a MERGE statement to handle upsert operations. It's best to refer to each system's specific guidelines for constructing these queries.
Was this article helpful?