Optimizing Language Data Management in SQLite with UPSERT

If no matching row is found, UPSERT inserts a new row with the provided values.
If a row with the same unique identifier (enforced by a UNIQUE constraint) already exists, the operation updates that existing row with the new values you're trying to insert.
You write an INSERT statement with the UPSERT keyword.

There are a few key points to remember about SQLite's UPSERT:

The syntax is similar to PostgreSQL's UPSERT implementation.
It was introduced in version 3.24.0 (released June 4th, 2018), so make sure you're using a compatible version of SQLite to use UPSERT.

INSERT OR REPLACE INTO my_table (column1, column2) VALUES (?, ?)
  OR RAISE(constraint failed);

INSERT INTO my_table (column1, column2) VALUES (?, ?)
ON CONFLICT(unique_column) DO UPDATE SET column1 = excluded.column1, column2 = excluded.column2;

This version uses the ON CONFLICT clause to specify the behavior when a conflict occurs. In this case, it updates the column1 and column2 values of the existing row with the values being inserted (excluded.column1 and excluded.column2 refer to the attempted insert values). You can customize the update logic within the DO UPDATE clause to suit your needs.

Updating a specific column based on conflict

CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT UNIQUE, email TEXT);

-- Insert a new user
INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');

-- Update email for existing user
INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]')
ON CONFLICT(username) DO UPDATE SET email = excluded.email;

This code creates a users table with columns for id, username (unique), and email. It then inserts a new user and demonstrates how to update the email for an existing user using UPSERT. The ON CONFLICT clause specifies that if a username conflict occurs, only the email column is updated with the new value from the attempted insert.

Updating multiple columns based on conflict

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT UNIQUE, price REAL, stock INTEGER);

-- Insert a new product
INSERT INTO products (name, price, stock) VALUES ('T-Shirt', 19.99, 100);

-- Update price and stock for existing product
INSERT INTO products (name, price, stock) VALUES ('T-Shirt', 24.99, 80)
ON CONFLICT(name) DO UPDATE SET price = excluded.price, stock = excluded.stock;

This code creates a products table and demonstrates updating multiple columns (price and stock) when a conflict occurs on the unique name column.

Handling potential errors

BEGIN TRANSACTION;

INSERT INTO accounts (username, balance) VALUES ('jane_smith', 1000)
ON CONFLICT(username) DO UPDATE SET balance = balance + excluded.balance;

COMMIT;

This code showcases handling potential errors. It wraps the UPSERT statement within a transaction. If the update within the ON CONFLICT clause fails due to a reason like data type mismatch, the entire transaction can be rolled back using ROLLBACK (not shown here).

Separate INSERT and UPDATE statements

This is the most basic approach. You can write two separate SQL statements:

An UPDATE statement wrapped in a conditional check using SELECT to update the existing row if the insert fails due to a unique constraint violation.
An INSERT statement to try inserting a new row.

CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT UNIQUE, email TEXT);

-- Attempt to insert a new user
INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');

-- Update email if username already exists
UPDATE users SET email = '[email protected]'
WHERE username = 'john_doe' AND id NOT IN (SELECT id FROM users WHERE username = 'john_doe');

This approach works, but it requires two separate queries and can be less efficient than a single UPSERT statement.

Triggers

You can create a trigger that fires on attempted INSERT operations. The trigger can check for conflicts and perform an update on the existing row if necessary.

CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT UNIQUE, email TEXT);

CREATE TRIGGER update_on_conflict AFTER INSERT ON users
BEGIN
  UPDATE users SET email = NEW.email
  WHERE username = NEW.username AND id != NEW.id;
END;

-- Insert triggers fire automatically when you try to insert a new row
INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');

This approach offers more flexibility but can be more complex to manage and can impact performance for simple inserts.

REPLACE statement (limited use)

SQLite offers a REPLACE statement that can be used for a specific scenario. It deletes the existing row with the conflicting unique identifier and then inserts a new row with the provided values. However, use this with caution as it performs a delete followed by an insert, which might not be desirable in all situations.

CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT UNIQUE, email TEXT);

-- Replace the entire row if username already exists
REPLACE INTO users (username, email) VALUES ('john_doe', '[email protected]');

Use REPLACE cautiously, only if you intend to completely replace the existing row.
If you need more control over conflict resolution logic, triggers offer flexibility.
For simple UPSERT needs, separate INSERT and UPDATE statements might suffice.