SQLite UPSERT with ON CONFLICT: INSERT or UPDATE in One Query

If you need to insert a row when it does not exist and update it when it does, SQLite UPSERT with ON CONFLICT is the tool you want.

One of the most common patterns in application development is the "Update if exists, insert if new" workflow.

Traditionally, you might write code like this in Python or JavaScript:

1. Current: SELECT * FROM user_stats WHERE user_id = 1
2. If result exists: UPDATE user_stats SET visits = visits + 1 WHERE user_id = 1
3. Else: INSERT INTO user_stats (user_id, visits) VALUES (1, 1)

This is bad for performance (2 queries) and bad for concurrency (what if another user inserts between step 1 and 3?).

SQLite (since version 3.24) solves this elegantly with the ON CONFLICT clause, commonly known as UPSERT. It lets you write one atomic statement instead of splitting the logic across a SELECT, an UPDATE, and a fallback INSERT.

A Real Business Scenario: Daily Profile Sync

Imagine you ingest user profile updates from a billing provider every few minutes.

Each payload contains:

• a stable external ID
• the latest subscription tier
• the latest sync timestamp

Your application should not create duplicate customer rows every time the provider retries a webhook. It also should not run a SELECT first, because retry storms and concurrent workers make that pattern fragile.

UPSERT is a good fit here because the business rule is explicit: one row per external customer ID.

INSERT INTO customer_sync (external_id, plan_name, synced_at)
VALUES ('cus_123', 'pro', '2026-04-20T10:30:00Z')
ON CONFLICT(external_id)
DO UPDATE SET
  plan_name = excluded.plan_name,
  synced_at = excluded.synced_at;

That statement is compact, retry-safe, and easier to reason about in logs than a read-then-write application sequence.

The Syntax of UPSERT

The basic structure adds a clause to your INSERT statement:

INSERT INTO table_name (col1, col2) 
VALUES (val1, val2)
ON CONFLICT(target_column) 
DO UPDATE SET col2 = val2;

You can also choose to do nothing:

INSERT INTO table_name (col1, col2) 
VALUES (val1, val2)
ON CONFLICT(target_column) 
DO NOTHING;

Interactive Example: Tracking User Visits

Let's maintain a table of user statistics. We want to simply "log a visit" without worrying about whether it's the user's first visit or their 100th.

Scenario:

1. User Alice visits (First time -> Insert).
2. User Bob visits (First time -> Insert).
3. User Alice visits again (Existing -> Update/Increment).

Understanding excluded

Did you notice excluded.last_visit in the example above?

• visit_count + 1 refers to the existing row's value.
• excluded.last_visit refers to the new value you were trying to insert (the one that got "excluded" because of the conflict).

This distinction is powerful. It allows you to mix old data with new data in your update logic.

Conditional Updates

You can even add a WHERE clause to your UPSERT logic.

For example, "Only update the last login date if the new date is actually newer than the old one":

INSERT INTO sync_status (device_id, sync_time)
VALUES ('phone_1', '2025-01-20')
ON CONFLICT(device_id)
DO UPDATE SET sync_time = excluded.sync_time
WHERE excluded.sync_time > sync_status.sync_time;

A Common Wrong Turn: INSERT OR REPLACE

Many SQLite beginners reach for INSERT OR REPLACE and assume it is just shorthand for UPSERT. It is not.

REPLACE works by deleting the conflicting row and inserting a new one. That can:

• change the rowid
• reset columns you forgot to include in the new INSERT
• trigger delete/insert side effects instead of an in-place update

This is the kind of bug that stays hidden until audit columns, foreign keys, or triggers start behaving strangely.

-- Looks convenient, but it is not the same as DO UPDATE
INSERT OR REPLACE INTO user_profiles (id, email, display_name)
VALUES (7, '[email protected]', 'New Name');

If the old row also had created_at, last_login_at, or related child rows, REPLACE can produce behavior you did not intend.

When UPSERT is the right choice

UPSERT is a strong fit when the write should be keyed by an explicit uniqueness rule and retries should stay safe.

Typical cases include:

• counters such as visits, likes, or inventory adjustments
• sync jobs keyed by a device ID, external ID, or natural business key
• API writes where the same request may be retried and should not create duplicates
• staging pipelines that may see repeated rows for the same primary or unique key

If the write logic depends on reading multiple rows first, applying cross-row business rules, or performing side effects outside the database, UPSERT alone is usually not the whole solution.

Performance and Boundary Notes

UPSERT is not magic. It still has to probe the unique index that defines the conflict target.

Practical implications:

• If the conflict target is missing a real PRIMARY KEY or UNIQUE index, the statement cannot work correctly.
• If the target row is wide and the DO UPDATE clause touches many columns, heavy retry traffic can still create write contention.
• If you bulk-load millions of rows, a staging table plus deduplication step may be easier to observe and recover than row-by-row UPSERTs.
• If your update expression is non-idempotent, such as counter = counter + excluded.counter, repeated source events can still overcount unless the input itself is deduplicated.

UPSERT solves the "which row should this write affect?" problem. It does not automatically solve upstream event duplication, business-rule conflicts, or batch-ingestion design.

When NOT to Use UPSERT

Do not default to UPSERT when the write depends on broader state than a single unique key.

Examples:

• approving a payment only if an account balance across several tables is still valid
• assigning a seat only if no other transaction grabbed the last remaining seat
• choosing between multiple candidate rows based on ranking or time windows

In those cases, you usually need an explicit transaction, a read step with locking semantics appropriate to your database, or a batch reconciliation workflow. Forcing everything into one UPSERT statement can hide real business complexity instead of removing it.

OR IGNORE vs ON CONFLICT

You might have seen INSERT OR IGNORE. How is that different?

• INSERT OR IGNORE: If any constraint fails (Unique, Not Null, Check), the insert is silently skipped. You can't perform an update.
• ON CONFLICT DO NOTHING: Specifically targets the conflict you specify.
• ON CONFLICT DO UPDATE: The true UPSERT, allowing you to modify the existing row.

Best Practices

1. Require a Unique Index: UPSERT relies on a conflict. You typically need a PRIMARY KEY or UNIQUE constraint on the column(s) you are checking.
2. Atomic Counters: This is the best way to maintain counters (like "likes", "views", "inventory") because it's atomic and race-condition free within the database engine.
3. Idempotency: UPSERT allows you to make your API endpoints idempotent. A client can retry a "Create User" request safely without causing duplicate errors.

Official References

• SQLite UPSERT documentation for the exact ON CONFLICT ... DO UPDATE syntax and conflict-target rules.
• SQLite conflict resolution documentation for how SQLite treats IGNORE, REPLACE, and other conflict strategies.
• PostgreSQL INSERT documentation for a vendor reference on ON CONFLICT, excluded, and conditional updates in another major SQL engine.

Frequently Asked Questions

Do I need a primary key or unique index for SQLite UPSERT?

Yes. ON CONFLICT needs a real conflict target. In practice that means a PRIMARY KEY, a UNIQUE column, or a composite unique constraint that tells SQLite when two rows should be treated as the same logical record.

What is the difference between INSERT OR REPLACE and ON CONFLICT DO UPDATE?

INSERT OR REPLACE deletes the old row and inserts a new one, which can reset related values and trigger different side effects. ON CONFLICT DO UPDATE updates the existing row in place, which is usually the safer choice for counters, audit columns, and foreign-key relationships.

Can SQLite UPSERT target multiple columns?

Yes. If your table uses a composite unique constraint such as UNIQUE(user_id, course_id), your UPSERT can target that combined key so the conflict is resolved at the correct business level.

Related Articles

• Mastering SQL Constraints for the uniqueness and key rules that UPSERT depends on to work correctly.
• Dynamic SQL Best Practices for cases where UPSERT statements are assembled programmatically and should stay safe and reviewable.
• Understanding SQL Views: Your Virtual Tables Explained for the broader question of when logic should stay in reusable SQL objects versus write-time statements.

Conclusion

The ON CONFLICT clause converts complex application logic into a single, efficient, and safe SQL statement. Whether you are building counters, sync logs, or user profiles, mastering UPSERT is essential for modern SQLite development.