SQLite – INSERT IF NOT EXISTS ELSE UPDATE

SQLite is a popular choice for embedded database applications due to its lightweight nature and ease of use. One common scenario in SQLite is the need to insert a row into a table if it does not already exist and update it if it does.

In this article, We will explore how to achieve this efficiently by understanding the SQLite’s INSERT OR REPLACE statement with the help of examples and so on.

How to Insert Record only if the Record Doesn’t Exist?

When working with SQLite databases, there are often situations where we need to insert a new record if it doesn’t exist, or update an existing record if it does.

This can be achieved using a combination of INSERT OR REPLACE statements that provide a more concise way to handle it.

Syntax:

INSERT OR REPLACE INTO users (id, name, email) VALUES (value1, value2, value3);

Explanation:

• users is the name of the table.
• (id, name, email) specifies the columns to insert or replace.
• VALUES (value1, value2, value3) defines the values to be inserted or replaced, typically provided as parameters.

To understand How to Insert Record only if the Record Doesn’t Exist we need a table on which we will perform various operations and queries. Here we will consider a table called users which contains id, username and email as Columns.

Create Table:

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE,
    email TEXT
);

Insert Data:

INSERT INTO users (username, email) VALUES
    ('minal', 'minal@gmail.com'),
    ('mahi', 'mahi@gmail.com'),
    ('vardhana', 'vardhana@gmail.com');

Output:

Users Table

Explanation: Table got created successfully.

Examples of How to Insert Record only if the Record Doesn’t Exist

Below are the some example queries of how to insert a new data if it doesn’t exist or update it if exist.

Example 1: Insert A New User Profile If It Doesn’t Exist, Or Update The Email If The Username Already Exists

INSERT OR REPLACE INTO users (id, username, email) VALUES (1, 'john doe', 'john@gmail.com');

Output:

Output

Explanation:

• INSERT OR REPLACE INTO users: It says that you want to insert a new record into the users’ table or if the record already exists and the primary key (in this case, id) is the same then to replace it.
• (id, username, email): This means the columns in which data will be saved.
• VALUES (1, ‘john doe’, ‘john@gmail.com’): This defines where in the rows and columns to insert these values. As we can see that the values get updated of primary key 1.

Example 2: Insert A New User Profile If It Doesn’t Exist, Or Update Both Username And Email If The Id Already Exists

INSERT OR REPLACE INTO users (id, username, email) VALUES (2, 'minal', 'minal@gmail.com');

Output:

Output

Explanation:

• INSERT OR REPLACE INTO users: It says that you want to insert a new record into the users’ table or if the record already exists and the primary key (in this case, id) is the same then to replace it.
• (id, username, email): It is the explanation of which columns to where the data will be inserted.
• VALUES (2, ‘minal’, ‘minal@gmail.com’): This specifies the values to be inserted into the corresponding columns.

Example 3: Insert A New User Profile If It Doesn’t Exist, Or Update The Email If The Username Already Exists

INSERT OR REPLACE INTO users (id, username, email) VALUES (4,'Gaurav', 'gaurav@gmail.com' );

Output:

Output

Explanation:

• INSERT OR REPLACE INTO users: It says that you want to insert a new record into the users’ table or if the record already exists and the primary key (in this case, id) is the same then to replace it.
• (id, username, email): This indicates the columns into which the data should be inserted or copied.
• VALUES (4, ‘Gaurav’, ‘gaurav@gmail.com’): The value corresponding to 4 will be inserted into the “id” column, ‘Gaurav’ into the “username” column, and ‘gaurav@gmail.com’ into the “email” column.

Example 4: Update The Email For An Existing User Profile Based On The Username

UPDATE users SET email = 'new_email@example.com' WHERE username = 'john_doe';

Output:

Output

Explanation:

• UPDATE users: The query now refers to the table that will be modified, and this is table “users”.
• SET email = ‘new_email@example.com’: It replaces the original ’email’ column with ‘new_email@example.com’.
• WHERE username = ‘john_doe’: This method prevents the unnecessary updating of records by only updating records where the “username” column is equal to ‘john_doe’..

Conclusion

SQLite is a dataset whose INSERT OR REPLACE function acts as a particular construct which enabling one to insert data that doesn’t exist or update it when it does exist. According to this function, developers can set up an automated data table they managed and so the SQLite databases in the applications will perform better and with precision.