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.

 

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.

 

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

 

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.

 

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.

 

Now the records of Person table are as follows:

 

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

 

Or you can also try following statement:

 

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.

 

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.

 

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