SQLite Replace Statement

  Total Views : 47
  Comments : 0
 
  Keywords : SQLite replace command to insert or replace rows with example, How to use sqlite replace command for insert operations with example

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 REPLACE statement is used to delete and reinsert existing rows. The REPLACE command in SQLite is an alias for INSERT OR REPLACE variant of the INSERT command. 

 

The idea behind 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 process in two steps.

 

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

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

Syntax of SQLite Replace Statement

Following is the syntax of SQLite Replace command.

 

INSERT OR REPLACEINTO 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 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 email column of Person table. Following SQLite statement is used to create unique index on column email of 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 SQLite Replace statement we can implement this functionality easily.

 

The following SQLite REPLACE statement will insert a new row into Person table because the person email yamini@gmail.com is not exists in 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 record in 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 above SQLite Replace statement following functionality checks will happen.

 

  • First, SQLite will check for UNIQUE constraint.
  • 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 existing row, the SQLite Replace Statement will insert new row with the data provided in SQLite query.

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

 

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

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

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

 

  • First, SQLite Replace command will check for PRIMARY KEY constraint.
  • Second, Replace statement will delete record where SSID = 2 because its already exist.
  • After deleting 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 SQLite Replace statement to get required data based on our requirements.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User