Summary: in this tutorial, you will learn how to use the SQLite REPLACE statement to insert or replace the existing row in a table.
Introduction to the SQLite REPLACE statement
The idea of the REPLACE statement is that when a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE statement:
In the second step, if any constraint violation e.g., NOT NULL constraint occurs, the REPLACE statement will abort the insert and rollback the transaction.
The following illustrates the syntax of the REPLACE statement.
1 2 | INSERT OR REPLACE INTO table(column_list) VALUES(value_list); |
Or in a shorter form:
1 2 | REPLACE INTO table(column_list) VALUES(value_list); |
Let’s take a look at some examples of using the SQLite REPLACE statement to understand how it works.
The SQLite REPLACE statement examples
First, create a new table named positions with the following structure.
1 2 3 4 5 | CREATE TABLE IF NOT EXISTS positions ( id integer PRIMARY KEY, title text NOT NULL, min_salary numeric ); |
Second, insert some rows into the positions table.
1 2 3 4 | INSERT INTO positions (title, min_salary) VALUES ('DBA', 120000), ('Developer', 100000), ('Architect', 150000); |
Third, verify the insert using the following SELECT statement.
1 2 3 4 | SELECT id,title,min_salary FROM positions; |
The following statement creates a unique index on the title column of the positions table to ensure that it doesn’t have any duplicate position title:
1 | CREATE UNIQUE INDEX idx_positions_title ON positions (title); |
Suppose, you want to add a position into the positions table if it does not exist, if the position exists, update the current one.
The following REPLACE statement inserts a new row into the positions table because the position title Full Stack Developer is not in the positions table.
1 2 3 | REPLACE INTO positions (title, min_salary) VALUES ('Full Stack Developer', 140000); |
You can verify the REPLACE operation using the SELECT statement.
1 2 3 4 | SELECT id,title,min_salary FROM positions; |
See the following statement.
1 2 3 | REPLACE INTO positions (title, min_salary) VALUES ('DBA', 170000); |
First, SQLite checked the UNIQUE constraint.
Second, because this statement violated the UNIQUE constraint by trying to add the DBA title that already exists, SQLite deleted the existing row.
Third, SQLite inserted a new row with the data provided by the REPLACE statement.
Notice that the REPLACE statement means INSERT or REPLACE, not INSERT or UPDATE.
See the following statement.
1 2 3 | REPLACE INTO positions (id, min_salary) VALUES (2, 110000); |
What the statement tried to do is to update the min_salary for the position with id 2, which is the developer.
First, the position already with id 2 already exists, the REPLACE statement removes it.
Then, SQLite tried to insert a new row with two columns: ( id, min_salary). However, it violates the NOT NULL constraint of the title column. Therefore, SQLite rollbacks the transaction.
If the title column does not have the NOT NULL constraint, the REPLACE statement will insert a new row whose the title column is NULL.
In this tutorial, we have shown you how to use the SQLite REPLACE statement to insert or replace a row in a table.