How to Use UPSERT in SQLite

Key Insights

SQLite’s UPSERT (available since version 3.24.0) combines INSERT and UPDATE operations into a single statement, eliminating race conditions and reducing code complexity when handling duplicate key conflicts.
The excluded table reference lets you access the values from the failed INSERT attempt, enabling sophisticated update logic that combines new and existing data during conflicts.
Properly indexed conflict targets are essential for UPSERT performance—SQLite requires either a PRIMARY KEY, UNIQUE constraint, or unique index to determine when conflicts occur.

Introduction to UPSERT

UPSERT is a portmanteau of “UPDATE” and “INSERT” that describes an atomic operation: attempt to insert a row, but if it conflicts with an existing row (based on a unique constraint), update that row instead. This pattern is incredibly common in real-world applications—think user profiles, configuration settings, or cache tables where you want to store a value regardless of whether it already exists.

Before UPSERT, you had three options: run a SELECT to check existence first (vulnerable to race conditions), use INSERT OR REPLACE (which deletes and reinserts, losing unspecified column values), or handle the error from a failed INSERT and retry with UPDATE. All these approaches are clunky and error-prone.

SQLite introduced proper UPSERT support in version 3.24.0 (June 2018) with the ON CONFLICT clause. This gives you precise control over conflict resolution without the downsides of older workarounds.

Basic UPSERT Syntax

The fundamental UPSERT syntax uses INSERT...ON CONFLICT...DO UPDATE SET. Here’s the simplest form:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    name TEXT,
    updated_at INTEGER
);

INSERT INTO users (id, email, name, updated_at)
VALUES (1, 'alice@example.com', 'Alice', unixepoch())
ON CONFLICT(id) DO UPDATE SET
    name = excluded.name,
    updated_at = excluded.updated_at;

This statement attempts to insert a new user. If a user with id = 1 already exists, it updates the name and updated_at columns instead. The excluded keyword refers to the row you tried to insert—more on this later.

The key components are:

Standard INSERT statement with values
ON CONFLICT(column_name) specifying which constraint triggers the update
DO UPDATE SET defining what to update when conflicts occur

Understanding Conflict Targets

The conflict target tells SQLite which constraint violation should trigger the UPSERT behavior. You have several options:

-- Using PRIMARY KEY as conflict target
INSERT INTO users (id, email, name)
VALUES (1, 'alice@example.com', 'Alice')
ON CONFLICT(id) DO UPDATE SET name = excluded.name;

-- Using UNIQUE constraint as conflict target
INSERT INTO users (id, email, name)
VALUES (2, 'alice@example.com', 'Alice Smith')
ON CONFLICT(email) DO UPDATE SET name = excluded.name;

-- Using multiple columns (composite unique constraint)
CREATE TABLE user_settings (
    user_id INTEGER,
    setting_key TEXT,
    setting_value TEXT,
    UNIQUE(user_id, setting_key)
);

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (1, 'theme', 'dark')
ON CONFLICT(user_id, setting_key) DO UPDATE SET
    setting_value = excluded.setting_value;

The conflict target must correspond to an actual constraint or unique index. You cannot use ON CONFLICT(random_column) unless that column has a UNIQUE constraint or is part of a unique index.

For tables with multiple unique constraints, specifying the conflict target is crucial. Without it, SQLite won’t know which constraint violation should trigger the UPSERT:

-- This works when there's only one possible conflict
INSERT INTO users (id, email, name)
VALUES (1, 'alice@example.com', 'Alice')
ON CONFLICT DO UPDATE SET name = excluded.name;

-- But be explicit when you have multiple unique constraints
INSERT INTO users (id, email, name)
VALUES (1, 'alice@example.com', 'Alice')
ON CONFLICT(email) DO UPDATE SET name = excluded.name;

UPDATE Actions with UPSERT

The excluded table is a virtual table containing the values from your INSERT statement. It’s the secret sauce that makes UPSERT powerful:

CREATE TABLE products (
    sku TEXT PRIMARY KEY,
    name TEXT,
    price REAL,
    stock INTEGER,
    last_updated INTEGER
);

-- Update price and stock, but keep the existing name
INSERT INTO products (sku, name, price, stock, last_updated)
VALUES ('WIDGET-001', 'Super Widget', 29.99, 100, unixepoch())
ON CONFLICT(sku) DO UPDATE SET
    price = excluded.price,
    stock = excluded.stock,
    last_updated = excluded.last_updated;

You can also use conditional logic in the UPDATE portion with a WHERE clause:

-- Only update if the new price is different
INSERT INTO products (sku, name, price, stock, last_updated)
VALUES ('WIDGET-001', 'Super Widget', 29.99, 100, unixepoch())
ON CONFLICT(sku) DO UPDATE SET
    price = excluded.price,
    stock = excluded.stock,
    last_updated = excluded.last_updated
WHERE products.price != excluded.price;

-- Only update if the new stock is higher
INSERT INTO products (sku, name, price, stock, last_updated)
VALUES ('WIDGET-001', 'Super Widget', 29.99, 150, unixepoch())
ON CONFLICT(sku) DO UPDATE SET
    stock = excluded.stock,
    last_updated = excluded.last_updated
WHERE excluded.stock > products.stock;

This WHERE clause operates on the UPDATE, not the INSERT. If the condition is false, the UPDATE doesn’t happen, but the statement still succeeds.

DO NOTHING vs DO UPDATE

Sometimes you want to ignore duplicates entirely rather than updating them. That’s where DO NOTHING comes in:

-- Insert only if it doesn't exist; otherwise, do nothing
INSERT INTO users (id, email, name)
VALUES (1, 'alice@example.com', 'Alice')
ON CONFLICT(id) DO NOTHING;

This is useful for idempotent operations or when you want to preserve the first value that was inserted:

CREATE TABLE api_cache (
    cache_key TEXT PRIMARY KEY,
    cache_value TEXT,
    created_at INTEGER
);

-- Only cache if not already present
INSERT INTO api_cache (cache_key, cache_value, created_at)
VALUES ('user:123', '{"name":"Alice"}', unixepoch())
ON CONFLICT(cache_key) DO NOTHING;

Here’s a side-by-side comparison:

-- DO NOTHING: Preserve existing data
INSERT INTO users (id, email, name, created_at)
VALUES (1, 'alice@example.com', 'Alice', unixepoch())
ON CONFLICT(id) DO NOTHING;
-- If id=1 exists, nothing changes

-- DO UPDATE: Always update to new values
INSERT INTO users (id, email, name, updated_at)
VALUES (1, 'alice@example.com', 'Alice Smith', unixepoch())
ON CONFLICT(id) DO UPDATE SET
    email = excluded.email,
    name = excluded.name,
    updated_at = excluded.updated_at;
-- If id=1 exists, it gets updated

Common Patterns and Real-World Use Cases

Let’s look at a complete example with a user preferences table:

CREATE TABLE user_preferences (
    user_id INTEGER,
    preference_key TEXT,
    preference_value TEXT,
    updated_at INTEGER,
    PRIMARY KEY (user_id, preference_key)
);

-- User changes their theme preference
INSERT INTO user_preferences (user_id, preference_key, preference_value, updated_at)
VALUES (42, 'theme', 'dark', unixepoch())
ON CONFLICT(user_id, preference_key) DO UPDATE SET
    preference_value = excluded.preference_value,
    updated_at = excluded.updated_at;

-- Batch update multiple preferences
INSERT INTO user_preferences (user_id, preference_key, preference_value, updated_at)
VALUES 
    (42, 'theme', 'dark', unixepoch()),
    (42, 'language', 'en', unixepoch()),
    (42, 'notifications', 'enabled', unixepoch())
ON CONFLICT(user_id, preference_key) DO UPDATE SET
    preference_value = excluded.preference_value,
    updated_at = excluded.updated_at;

Another common pattern is incrementing counters or updating timestamps:

CREATE TABLE page_views (
    url TEXT PRIMARY KEY,
    view_count INTEGER DEFAULT 0,
    last_viewed INTEGER
);

-- Increment view count or initialize to 1
INSERT INTO page_views (url, view_count, last_viewed)
VALUES ('/blog/sqlite-upsert', 1, unixepoch())
ON CONFLICT(url) DO UPDATE SET
    view_count = page_views.view_count + 1,
    last_viewed = excluded.last_viewed;

Notice how we reference page_views.view_count (the existing value) and add 1 to it, rather than using excluded.view_count. This lets you combine old and new data intelligently.

Performance Considerations and Best Practices

UPSERT performance depends heavily on proper indexing. The conflict target must be indexed—either through a PRIMARY KEY, UNIQUE constraint, or explicit unique index. Without an index, SQLite cannot efficiently detect conflicts:

-- Good: PRIMARY KEY is automatically indexed
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE  -- Also automatically indexed
);

-- Also good: Explicit unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);

For high-throughput scenarios, wrap multiple UPSERTs in a transaction:

BEGIN TRANSACTION;

INSERT INTO products (sku, price, stock)
VALUES ('WIDGET-001', 29.99, 100)
ON CONFLICT(sku) DO UPDATE SET price = excluded.price, stock = excluded.stock;

INSERT INTO products (sku, price, stock)
VALUES ('WIDGET-002', 39.99, 50)
ON CONFLICT(sku) DO UPDATE SET price = excluded.price, stock = excluded.stock;

-- ... more UPSERTs ...

COMMIT;

This dramatically improves performance by reducing disk I/O and ensuring atomicity across multiple operations.

Version compatibility matters: UPSERT requires SQLite 3.24.0 or later. If you’re supporting older versions, you’ll need fallback logic. Check your SQLite version:

SELECT sqlite_version();

Avoid INSERT OR REPLACE as a substitute for UPSERT. While it handles duplicates, it actually deletes the old row and inserts a new one, which means any columns you don’t specify get reset to their defaults. UPSERT’s DO UPDATE only modifies the columns you explicitly set.

Finally, be mindful of triggers. UPSERT can fire both INSERT and UPDATE triggers depending on whether a conflict occurs. If your triggers have side effects, test thoroughly to ensure UPSERT behaves as expected in your schema.