SQLite Replace Statement

Here we will learn SQLite Replace statement with example and how to use SQLite Replace statements to delete and reinsert existing rows with example.

SQLite Replace Statement

In SQLite, the REPLACE statement is used to delete and reinsert existing rows. The REPLACE command in SQLite is an alias for the INSERT OR REPLACE variant of the INSERT command. 

 

The idea behind the use of SQLite REPLACE statement is when any UNIQUE or PRIMARY Key constraint is violated then the REPLACE command will delete the row that causes constraint violation and insert a new row.

 

Generally, in SQLite REPLACE command will perform a process in two steps.

 

  • First, it deletes the row which causes constraint violation.
  • Insert a new row.

Now, if any constraint violation is occurring in the second step i.e. while inserting a new row, it will not insert a row and rollback the transaction.

Syntax of SQLite Replace Statement

Following is the syntax of SQLite Replace command.

 

INSERT OR REPLACE INTO TABLE (column_list)

VALUES (value_list);

 

OR

 

REPLACE INTO table_name ( col1, col2 VALUES ( val1, val2 );

Example of SQLite Replace Statement

We will see how to use SQLite Replace command with example. For that first, create one table called Person and insert some data by using the following queries.

 

CREATE TABLE Person

(SSID INTEGER PRIMARY KEY,

first_name TEXT NOT NULL,

last_name TEXT NOT NULL,

email TEXT,

phone_no TEXT,

city TEXT);

 

INSERT INTO Person

values (1,'Vinay','Jariwala','vinay@gmail.com','898545','Vapi'),

(2,'Shweta','Rana','Shweta@gmail.com','888875','Valsad'),

(3,'Sonal','Menpara','Sonal@gmail.com','84697','Surat'),

(4,'Jagruti','Viras','jagu@gmail.com','656454','Daman'),

(5,'Yamini','Patel','rani@gmail.com','98788','Mumbai');

Now, we will verify the records of Person table by using following SQLite SELECT statement.

 

sqliteSELECT FROM Person;

 

SSID        first_name  last_name   email            phone_no    city

----------  ----------  ----------  ---------------  ----------  ----------

1           Vinay       Jariwala    vinay@gmail.com  898545      Vapi

2           Shweta      Rana        Shweta@gmail.com 888875      Valsad

3           Sonal       Menpara     Sonal@gmail.com  84697       Surat

4           Jagruti     Viras       jagu@gmail.com   656454      Daman

5           Yamini      Patel       rani@gmail.com   98788       Mumbai

Now, we will create an index on the email column of the Person table. The following SQLite statement is used to create a unique index on the column email of the Person table.

 

CREATE UNIQUE INDEX idx_person_email ON Person(email);

Now we will implement restriction to add person details in Person table like if user email already exists means it should update that particular row otherwise need to insert new user details. By using the SQLite Replace statement we can implement this functionality easily.

 

The following SQLite REPLACE statement will insert a new row into the Person table because the person email yamini@gmail.com does not exist in the Person table.

 

REPLACE INTO Person (SSID,first_name,last_name,email,city)

Values (6,'Yami','Patel','yamini@gmail.com','Surat');

Now the records of Person table are as follows:

 

sqliteSELECT FROM Person;

 

SSID        first_name  last_name   email            phone_no    city

----------  ----------  ----------  ---------------  ----------  ----------

1           Vinay       Jariwala    vinay@gmail.com  898545      Vapi

2           Shweta      Rana        Shweta@gmail.co  888875      Valsad

3           Sonal       Menpara     Sonal@gmail.com  84697       Surat

4           Jagruti     Viras       jagu@gmail.com   656454      Daman

5           Yamini      Patel       rani@gmail.com   98788       Mumbai

6           Yami        Patel       yamini@gmail.com             Surat

Now we will try to insert existing records in the Person table with REPLACE statement like as shown below.

 

REPLACE INTO Person (SSID,first_name,last_name,email,city)

Values (6,'Yamini','Shah','yamini@gmail.com','Baroda');

Or you can also try following statement:

 

INSERT OR REPLACE INTO Person (SSID,first_name,last_name,email,city)

Values (6,'Yamini','Shah','yamini@gmail.com','Baroda');

When we execute the above SQLite Replace statement following functionality checks will happen.

 

  • First, SQLite will check for UNIQUE constraints.
  • Second, the given insert statement violated the UNIQUE constraint because we are trying to add yamini@gmail.com email that already exists so SQLite Replace command will delete the existing row.
  • After deleting the existing row, the SQLite Replace Statement will insert a new row with the data provided in the SQLite query.

In the above section, we have discussed a UNIQUE key violation in the SQLite REPLACE statement. Now, let’s look at the example of PRIMARY KEY violation in the REPLACE statement.

 

REPLACE INTO Person (SSID,first_name,last_name,email,city)

Values (2,'Shweta','Jariwala','shweta@gmail.com','Vapi');

When we execute the above SQLite Replace statement following is the process that will happen for Primary Key constraint.

 

  • First, the SQLite Replace command will check for PRIMARY KEY constraint.
  • Second, Replace statement will delete record where SSID = 2 because its already exist.
  • After deleting the existing record, it will insert new record SSID = 2 with new data in which we have not inserted phone no of person and last name is Jariwala instead of Rana and also city as Vapi instead of Valsad

So let’s verify Person table records by using SQLite SELECT statement.

 

sqliteSELECT FROM Person;

 

SSID        first_name  last_name   email            phone_no    city

----------  ----------  ----------  ---------------  ----------  ----------

1           Vinay       Jariwala    vinay@gmail.com  898545      Vapi

2           Shweta      Jariwala    shweta@gmail.com             Vapi

3           Sonal       Menpara     Sonal@gmail.com  84697       Surat

4           Jagruti     Viras       jagu@gmail.com   656454      Daman

5           Yamini      Patel       rani@gmail.com   98788       Mumbai

6           Yamini      Shah        yamini@gmail.com             Baroda

This is how we can use the SQLite Replace statement to get the required data based on our requirements.

Topics Covered