REPLACE Command

REPLACE is SQLite shorthand for conflict-handling insert behavior.

It can look like an "upsert," but its semantics are important: on conflict, SQLite deletes the old row, then inserts a new one.

Core Concepts

flowchart LR
  A[REPLACE INTO ...] --> B{Unique/PK conflict?}
  B -->|No| C[Insert new row]
  B -->|Yes| D[Delete conflicting row]
  D --> E[Insert replacement row]

Command form	Meaning
REPLACE INTO ...	Insert or replace conflicting row
INSERT OR REPLACE INTO ...	Equivalent behavior

Code Examples

-- Setup: email must be unique.
CREATE TABLE contacts_replace (
  contact_id INTEGER PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  full_name  TEXT NOT NULL
);

INSERT INTO contacts_replace (email, full_name)
VALUES ('ada@example.com', 'Ada Lovelace');

Expected output
Table created and one row inserted.

-- Conflict on email triggers replace behavior.
REPLACE INTO contacts_replace (email, full_name)
VALUES ('ada@example.com', 'Ada Byron');

Expected output
Existing conflicting row is replaced by a new row.

-- Inspect current table contents.
SELECT contact_id, email, full_name
FROM contacts_replace;

contact_id	email	full_name
e.g. new id	ada@example.com	Ada Byron

SQLite-Specific Nuances

SQLite Nuance

REPLACE is delete-then-insert behavior, not in-place update.

That can change primary keys and interact differently with foreign keys and triggers.

Common Pitfalls / Best Practices

Pitfall

Using REPLACE when you expect stable row identity.

You may unintentionally create new primary key values.

Best Practice

Use REPLACE only when delete-then-insert semantics are acceptable for your data model.

Quick Challenge

Write a statement that replaces a row in contacts_replace using email conflict on alan@example.com.

View Solution

REPLACE INTO contacts_replace (email, full_name)
VALUES ('alan@example.com', 'Alan Turing');

Expected output
A row for alan@example.com exists after execution (inserted or replaced).