How to insert row if not exists in MySQL

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

1. Using the INSERT IGNORE statement
2. Using the ON DUPLICATE KEY UPDATE clause
3. Or using the REPLACE statement

Keep in mind that before you create an insert row 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 MySQL will insert duplicate values just fine, and the following tutorials won’t work as intended.

1. Use the INSERT IGNORE statement

The MySQL INSERT IGNORE statement is used to insert a new row and ignore execution errors causes by the statement.

This statement changes 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 value specified for the unique column doesn’t already exist.

2. Use the ON DUPLICATE KEY UPDATE clause

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

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 the primary key is a duplicate, 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.

3. Use the REPLACE statement

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 deleted, and a new row with the above values is inserted.

Conclusion

When you need to insert a new row only if that row not exists, then the three solutions above are your best options.

I hope this tutorial is useful. Feel free to modify the queries above for your project! 😉