MySQL – How to Insert a New Row Only if Data Does not Exist

MySQL is a famous open-source RDBMS to store data in databases. Inserting data into tables of your database is a common task. Sometimes, users do not want to insert duplicated data to eliminate redundancy. MySQL allows users to insert data only if similar data does not already exist.

This post will discuss inserting a new row only if data does not exist already in the MySQL table. Before beginning with this post, ensure you are logged into your MySQL local server and have chosen a database where you want to work.

Insert a new Row Only if Data do not Exist Using the “INSERT IGNORE” Statement

Well, the answer to the question in the title is pretty simple and straightforward. Simply use the “INSERT” keyword along with the “IGNORE” Keyword. This will make sure that the new data or the insert query is only executed when no previous entry of the data is found in MySQL. To get a brief overview of this, take a look at the following syntax:

Let’s see an example if the user wants to enter a row value “1”, “John” and “Doe” in the “Customer” table for the columns “Id,” “FirstName” and “LastName” respectively. Run this query to insert these values if any if no row having these values exists already:

The output contains two tables displaying data of the table before and after executing the query. You can notice nothing is changing in the table because the row already existed so MySQL has successfully ignored the insertion process without displaying the error:

Insert a new Row Only if Data do not Exist Using the “WHERE NOT EXISTS” Clause

In MySQL, the “WHERE NOT EXIST” clause prevents the insertion of rows if they already exist in the table when it is used in the “INSERT INTO” statement with a subquery to define a condition. When this clause is used with the subquery “(SELECT * FROM [table-name] [column-name]=[expression]);” checks if a row exists in the table that fulfills the condition. If a row exists, the “WHERE NOT EXISTS” clause will return a false value, and the “SELECT” statement will not return any rows. As a result, the row will not be inserted into the table. The syntax is provided below:

Let’s see an example if the user wants to insert a row in the employee table containing values “1”, “Americanas” and “Henriot” in the columns “id”, “CompanyName” and “ContactName” respectively. But only if a row with an id of “1” is not found in the table or it’s not existent. In that particular case, the query will become:

The output displays a new row that is not inserted as the row with the “id” equal to “1” already existed.

Insert a New Row Only if Data do not Exist Using the “ON DUPLICATE KEY UPDATE” Clause

Well, another way is to use the “ON DUPLICATE KEY UPDATE” clause in MySQL. As this clause will be used with the “INSERT INTO” query forming an “if-else” execution. So this means that the data inside the table will only be updated if it is unique. Otherwise, no change would occur. The general syntax for this is as follows:

Let’s see an example, to insert values in “2”, “Pascale”, “Nixon”, “London”, “UK” and “(171) 555-7788” in the table “Customer” for the columns “Id”, “FirstName”, “LastName”, “city”, “country” and “phone” respectively. If the row does not exist already, it will be inserted. Otherwise, it will update the values where the “Id=Id” condition meets. Run this query:

This is the table before running the query, you can see “Id” equal to “2” does not exist:

After running the query, this query will insert new rows in the table:

You have successfully inserted a new row, as data does not exist before running the query.

Conclusion

In MySQL, insert a new row only if data do not exist already by using the “INSERT IGNORE” statement or “WHERE NOT EXISTS” clause in the “INSERT INTO” statement. Another way of doing this is using the “ON DUPLICATE KEY UPDATE” clause in the “INSERT INTO” statement to update if the row already exists. Otherwise, add a new row. The post discussed how to insert a new row only if data does not exist.