REPLACE

The REPLACE statement inserts a row into a table. If the row violates a UNIQUE or PRIMARY KEY constraint, the existing conflicting row is deleted before the new row is inserted.

Syntax

REPLACE INTO table-name [(column-name [, column-name ...])]
VALUES (expression [, expression ...]) [, (expression [, expression ...]) ...]

REPLACE INTO table-name [(column-name [, column-name ...])]
select-statement

Description

REPLACE is equivalent to INSERT OR REPLACE. When a REPLACE inserts a row that would violate a UNIQUE or PRIMARY KEY constraint, it deletes the existing row that causes the conflict, then inserts the new row. If no constraint violation occurs, REPLACE behaves identically to INSERT.

How REPLACE Differs from INSERT

With a plain INSERT, a constraint violation causes the statement to fail with an error. With REPLACE, the conflicting row is silently deleted and the new row takes its place.

Because the conflicting row is deleted first, REPLACE may fire DELETE triggers on the old row and INSERT triggers on the new row.

Constraint Behavior

When multiple UNIQUE constraints exist on a table, REPLACE deletes every row that conflicts with the new row before inserting it. This can result in more than one row being deleted by a single REPLACE operation.

If a NOT NULL constraint is violated on a column that has no DEFAULT value, the REPLACE fails with an error.

Examples

Basic REPLACE

CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT);

INSERT INTO settings VALUES ('theme', 'light');
SELECT * FROM settings;
-- theme|light

-- The existing row is deleted and a new row is inserted
REPLACE INTO settings VALUES ('theme', 'dark');
SELECT * FROM settings;
-- theme|dark

REPLACE deletes and re-inserts

Unlike UPDATE, REPLACE removes the old row entirely. Columns not specified in the statement receive their default values:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TEXT DEFAULT (datetime('now'))
);

INSERT INTO users VALUES (1, 'Alice', '[email protected]', '2025-01-01');

-- This deletes row with id=1 and inserts a completely new row.
-- The created_at column gets its DEFAULT value, not '2025-01-01'.
REPLACE INTO users (id, name, email) VALUES (1, 'Alice', '[email protected]');

SELECT id, name, email FROM users;
-- 1|Alice|[email protected]

Multi-row REPLACE

CREATE TABLE inventory (sku TEXT PRIMARY KEY, quantity INTEGER);
INSERT INTO inventory VALUES ('A001', 10);
INSERT INTO inventory VALUES ('B002', 20);

REPLACE INTO inventory VALUES
    ('A001', 15),
    ('B002', 25),
    ('C003', 30);

SELECT * FROM inventory;
-- A001|15
-- B002|25
-- C003|30

See Also

• INSERT for adding rows without conflict resolution
• INSERT ... ON CONFLICT for conditional insert-or-update with control over which columns are updated
• UPDATE for modifying existing rows in place