SQLite Replace

SQLite REPLACE command is a powerful way to insert or update data. The command will first try to insert a new row. If the row already exists, it will update the row instead.

Syntax

Here is the syntax for the SQLite REPLACE command:

REPLACE INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Example

First of all, we create the customers table, then we add a unique index on the id column. If there is no uniqueness index, the REPLACE command will not work correctly.

create table customers(id int, name varchar(200), city varchar(250));
CREATE UNIQUE INDEX idx_id ON customers (id);

The next step is to populate the customer table with data.

insert into customers(id, name, city) values
(1, 'Tom', 'New York'),
(2, 'Olivia', 'New York'),
(3, 'Emma', 'Los Angeles');

The last step is to use SQLite REPLACE command to make changes in customers table. For example, the value of the city column will be changed from New York to NY, from Los Angeles to LA, and two new records will be inserted in the customers table.

REPLACE INTO customers(id, name, city) values
(1, 'Tom', 'NY'),
(2, 'Olivia', 'NY'),
(3, 'Emma', 'LA'),
(4, 'James', 'Chicago'),
(5, 'Sophia', 'San Diego');