How to Insert if Not Exists in MariaDB

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.

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:

Users Table

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:

Record Inserted

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”:

1. Using the INSERT IGNORE statement

INSERT IGNORE command can be used for inserting new records into a table, and duplicates won’t violate the unique constraints, thus they will be ignored.

Here is an example of the INSERT IGNORE statement to insert a row if not exist in the query with the help of the following query:

INSERT IGNORE INTO Users (user_id, name, email) VALUES (1, 'ratnala', 'ratnala1@gmail.com');

Output:

Using IGNORE Statement

Explanation: With the INSERT IGNORE sentence, MariaDB will add a row only if the values do not exist in the table. And it will give PRIMARY KEY violation warning explicitly for duplicate values.

2. On the DUPLICATE KEY UPDATE Clause

The ON DUPLICATE KEY UPDATE clause allows us to specify how MariaDB should handle duplicates when inserting new records.

Here is an illustrated example of the ON DUPLICATE KEY UPDATE clause which will be used at the end of a query for the INSERT clause as shown below:

INSERT INTO Users (user_id, name, email) VALUES (1, 'ratnala', 'ratnala1@gmail.com')
ON DUPLICATE KEY UPDATE name = 'minal', email = 'minal1@gmail.com';

Output:

DUPLICATE KEY UPDATE clause

Explanation:

• If a duplicate key is found in the input keys, MariaDB will run the UPDATE part of the statement and update existing record with specified values.
• If the duplicate key is not identified, MariaDB will insert a new record which will be represented by the given values.

3. Using Replace Statement

The REPLACE statement enables we to put a new record in a table. In case of the duplicate key, MariaDB gets rid of the old record and inserts a new one.

REPLACE INTO users (id, name, email) VALUES (1, 'ratnala', 'ratnala1@gmail.com');

Output:

Using Replace Statement

Explanation: If a duplicate key is found during insertion, MariaDB replaces the existing record with the new one, effectively deleting the old record and inserting the new one in its place.

MariaDB Update IF NOT EXISTS

If we want to do a MariaDB update operation under such conditions (IF NOT EXISTS or WHERE NOT EXISTS), you cannot directly use the respective clauses with the UPDATE statement. On the other hand, using subqueries and conditional logic can achieve the same functionality.

Example: Let’s update the email of a user only if there is no other user with the same email.

UPDATE Users SET email = 'new_email@gmail.com' WHERE user_id = 1
AND NOT EXISTS ( SELECT 1 FROM Users WHERE email = 'new_email@example.com' AND user_id != 1)

Output:

Update Email

Explanation:

• We’re updating the email for the user with id equal to 1.
• The NOT EXISTS subquery checks if there are no other users with the same email. If no such users exist, the condition is true, and the update operation proceeds.
• If there are other users with the same email, the condition evaluates to false, and the update operation does not occur for this user.

Conclusion

Inserting or updating records in an early efficient is the most necessary thing in database management The inclusion of SQL IF NOT EXISTS and ON DUPLICATE KEY UPDATE by MariaDB for you to quickly to deal with tasks of inserting new records or updating the existing ones if certain condition is met. Harnessing these features, you will be able to effectively maintain data integrity, and optimize operation in your database applications.