How to INSERT Data if not Exist in MariaDB?

When working with databases, it’s common to encounter situations where we need to either insert a new record or update an existing one based on certain conditions. MariaDB provides the SQL IF NOT EXISTS clause, which allows us to perform these operations in a single statement, making our code more concise and efficient.

1. Using the INSERT IGNORE statement
2. On the DUPLICATE KEY UPDATE clause
3. Using the REPLACE statement

Let’s set up an environment:

Let’s first create a table and insert some data into it.

CREATE TABLE Users 
(
    user_id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
);

INSERT INTO Users VALUES
(1, 'Ratnala', 'ratnala1@gmail.com'),
(2, 'Maram', 'maram2@gmail.com'),
(3, 'Medishetty', 'medishetty4@gmail.com'),
(4, 'Sukumar', 'suku8@gmail.com' ),
(5, 'Engula', 'engula5@gmail.com');

After Inserting, Our table looks like:

Example: Insert a record into users table.

INSERT INTO Users (user_id, name, email)
SELECT 6, 'Vivek', 'vivek@gmail.com'
WHERE NOT EXISTS (
    SELECT 6 FROM Users WHERE name = 'Vivek' OR email = 'vivek@gmail.com'
);

Output:

Explanation: As we can see that there is no record with the name of the vivek so it got inserted. We use the SELECT statement with the WHERE NOT EXISTS sub-query to check if any user with the provided name or email already exists in the users table. If there is no such record, the INSERT statement is executed and the new one is inserted into the table. If we want to do insertion in the table when the data does not exist then we need to create a work-around solution with the statements provided by MySQL. There are three ways we can use to “insert if not exist”:

When managing a database, the need often arises to either insert a new record or update an existing one. MariaDB provides a powerful tool to handle this situation efficiently: the SQL IF NOT EXISTS clause. This clause allows us to perform an INSERT operation only if the record does not already exist, or an UPDATE operation if it does. In this article, we will explore how to use the SQL IF NOT EXISTS clause in MariaDB to insert a record if not present otherwise update the record with the help of examples and so on.