MySQL - How to insert a new row only if data do not exist

Learn how to insert a new row only if the values do not exist in MySQL

Posted on September 03, 2021


MySQL doesn’t have a statement to insert a new row if the data do not exist.

This is because when you have table column(s) with PRIMARY KEY or UNIQUE constraint, then MySQL will throw an error each time your query inserts a new row with duplicate values for those columns.

Still, if you want to perform an insertion only when the data doesn’t already exist, then you need to create a work-around solution with the statements provided by MySQL.

There are three ways you can perform an “insert if not exists” query in MySQL:

  • Using the INSERT IGNORE statement
  • Using the ON DUPLICATE KEY UPDATE clause
  • Or using the REPLACE statement

Keep in mind that before you create an insert if not exists query, the MySQL table in use must already have one or more column(s) with PRIMARY KEY or UNIQUE constraint.

Without putting a constraint on the column that you want to be distinct, then the queries below won’t work as intended.

Insert a new row if not exists using INSERT IGNORE

The MySQL INSERT IGNORE statement is commonly used to change the error that MySQL throws when you use a normal INSERT statement into a warning so that the execution of your query isn’t interrupted.

For example, suppose you have a users table with the following data:

+---------+------------+-----------+
| user_id | first_name | last_name |
+---------+------------+-----------+
|     202 | Thor       | Odinson   |
|     204 | Loki       | Laufeyson |
+---------+------------+-----------+
2 rows in set (0.00 sec)

The user_id column above is a PRIMARY KEY field, so MySQL will throw an error if you try to insert a new row with one of the existing user_id values.

The query above uses the user_id value of 202:

INSERT INTO users 
  (user_id, first_name, last_name) 
VALUES 
  (202, "Tony", "Smith");

So MySQL will produce the following error:

ERROR 1062 (23000): Duplicate entry '202' for key 'users.PRIMARY'

The INSERT IGNORE statement will cause MySQL to do nothing when the insertion throws an error. If there’s no error, then a new row will be added to the table.

When you run the following query:

INSERT IGNORE INTO users 
  (user_id, first_name, last_name) 
VALUES 
  (202, "Tony", "Smith");

MySQL will produce the following output:

Query OK, 0 rows affected, 1 warning (0.00 sec)

With the INSERT IGNORE statement, MySQL will insert a new row only if the values don’t exist in the table.

Update existing row with unique value using ON DUPLICATE KEY UPDATE clause

The ON DUPLICATE KEY UPDATE clause allows you to update the row with the new values when a duplicate value is found in the UNIQUE index or PRIMARY KEY column.

The clause can be added by the end of a MySQL INSERT query as shown below:

INSERT INTO users
  (user_id, first_name, last_name) 
VALUES 
  (202, "Tony", "Smith")
ON DUPLICATE KEY UPDATE
  first_name = "Tony",
  last_name = "Smith";

First, MySQL will execute the regular insert query above. When a duplicate value is found, then MySQL will perform an update instead of an insert.

This query is useful if you still want the row to be updated when the unique value already exists.

Using the REPLACE statement for when a unique value already exists.

The REPLACE statement is an alternative to the ON DUPLICATE KEY UPDATE clause.

The statement essentially does two things:

  • MySQL will check the existing record to see if a row with the same unique value already exists
  • When a row with the same unique value is found, then the row will be deleted using the DELETE statement. MySQL will then run the INSERT statement to replace the deleted row.

When there’s no match in the existing record, then the REPLACE statement works like a regular INSERT statement.

Here’s an example of the REPLACE statement in action:

REPLACE INTO users
  (user_id, first_name, last_name) 
VALUES
  (202, "Tony", "Smith");

The row with the user_id value of 202 will be replaced with the above values if it already exists.

Conclusion

And those are the three ways you can perform a MySQL insert if not exists query.

Personally, I prefer to just do a normal INSERT query and handle the error thrown by MySQL from the backend side.

Programming languages like JavaScript, PHP, Ruby, and Python all have the try..catch block that you can use to handle errors from MySQL query execution.

But if you must handle the “insert if not exists” logic from the MySQL side, then the three methods above are your best options.

Feel free to modify the queries above for your project 😉

See also:

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.