MySQL Insert If Not Exists Statement

The INSERT statement in MySQL is one of the most commonly used and valuable statements in MySQL and other relational databases. It is used to insert new rows into a table. It allows you to specify the values to be inserted into the table and the columns into which the values should be inserted.

However, you may encounter an instance where the values you wish to insert exist in the table. Instead of adding a duplicate record or throwing an exception, you can tell the database engine to take another action.

In some relational databases, you can use the INSERT INTO IF NOT EXISTS, which tells the database engine only to insert the records if they exist in the target table. In MySQL, there is no INSERT INTO IF NOT EXISTS statement.

In this tutorial, you will discover how to implement an INSERT INTO IF NOT EXISTS clause in MySQL using the ON DUPLICATE KEY clause.

What is MySQL On Duplicate?

The ON DUPLICATE KEY UPDATE clause in MySQL is combined with the INSERT statement to specify an action to be taken if a duplicate key value is found in a unique index or primary key.

We can use this clause to specify a condition if a similar value exists in the database, for example, update the key with the new value if it already exists.

MySQL ON Duplicate Syntax

Below is the syntax for using the ON DUPLICATE KEY clause in MySQL.

INSERT INTO table (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column1=value1, column2=value2, ...;

Where the table is the table’s name to which you wish to insert the new row.

The column1, column2, … represents the names of the columns into which the values should be inserted.

The VALUES clause specifies the values to be inserted, which must be provided in the same order as the columns. The ON DUPLICATE KEY UPDATE clause specifies the action to be taken if a duplicate key value is found.

Sample Testing Table

Consider the table below, which stores information about popular programming languages.

CREATE TABLE programming_languages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    year_released INT UNSIGNED NOT NULL,
    creator VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL
);

This CREATE TABLE statement defines a new table called programming_languages with the following columns:

• id: An auto-incrementing primary key column.
• name: A VARCHAR column that stores the name of the programming language.
• year_released: An INT column that stores the year the programming language was released.
• creator: A VARCHAR column that stores the name of the person or organization that created the programming language.
• type: A VARCHAR column that stores the type of the programming language (e.g., procedural, functional, object-oriented, etc.).

We can then insert some sample data as shown:

INSERT INTO programming_languages (name, year_released, creator, type)
VALUES
    ('C', 1972, 'Dennis Ritchie', 'procedural'),
    ('Java', 1995, 'James Gosling', 'object-oriented'),
    ('Python', 1991, 'Guido van Rossum', 'interpreted'),
    ('C++', 1985, 'Bjarne Stroustrup', 'object-oriented'),
    ('JavaScript', 1995, 'Brendan Eich', 'interpreted'),
    ('C#', 2000, 'Microsoft', 'object-oriented'),
    ('PHP', 1994, 'Rasmus Lerdorf', 'interpreted'),
    ('Ruby', 1995, 'Yukihiro Matsumoto', 'interpreted'),
    ('Swift', 2014, 'Apple', 'object-oriented'),
    ('Go', 2009, 'Google', 'compiled');

Resulting table:

+----+------------+--------------+-------------------------+------------------+
| id | name       | year_released| creator                  | type             |
+----+------------+--------------+-------------------------+------------------+
|  1 | C          | 1972         | Dennis Ritchie           | procedural       |
|  2 | Java       | 1995         | James Gosling            | object-oriented  |
|  3 | Python     | 1991         | Guido van Rossum         | interpreted      |
|  4 | C++        | 1985         | Bjarne Stroustrup        | object-oriented  |
|  5 | JavaScript | 1995         | Brendan Eich             | interpreted      |
|  6 | C#         | 2000         | Microsoft                | object-oriented  |
|  7 | PHP        | 1994         | Rasmus Lerdorf           | interpreted      |
|  8 | Ruby       | 1995         | Yukihiro Matsumoto       | interpreted      |
|  9 | Swift      | 2014         | Apple                    | object-oriented  |
| 10 | Go         | 2009         | Google                   | compiled         |
+----+------------+--------------+-------------------------+------------------+

Using the ON DUPLICATE Clause

The example below demonstrates how to use the on duplicate clause with the insert statement:

INSERT INTO users (name, year_released, creator, type)
VALUES ('Rust', 2006, 'Graydon Hoare', 'compiled')
ON DUPLICATE KEY UPDATE name=VALUES(name), year_released=VALUES(year_released), creator=VALUES(creator), type=VALUES(year);

The statement above will attempt to insert the new record above into the table. If, by happenstance, a similar record already exists, the database engine will update the existing row with the new values.

Conclusion

In this tutorial, you learned how to use the on duplicate key clause in MySQL to prevent adding duplicate values to a table. You can also use the on duplicate key ingore instead of on duplicate key update clause to ignore errors instead of updating the row. However, this can ignore even critical errors which is not an advisable scenario in a database.