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