How to Insert if not exists in MySQL

In this post, I am going to demonstrate how to skip the insert statement for duplicate records in MySQL.

‘Insert if not exists’

Basically, there are two situations during bulk insertion:

  • First, during insertion if record is found then update with old value by new value.
    This I have already posted so please visit this page.

    Insert-Update, Merge statement in MySQL
  • Second, during insertion if record is found then skip new value for insert or overwrite.This I am going to explain in this post.


Using this statement you can perform actions like, ” Insert if not exists “.
First, make it your primary data by applying proper composite unique key or primary key.
If the same key is coming during new insertion, then INSERT IGNORE Statement simply skip that transaction.

Below is full demonstration:
I create two tables and transferring some duplicate data from one to another using INSERT IGNORE statement.

Let’s first create tables and sample data.

We create two tables, table first has no primary key and table second has a primary key.
You can find duplicate key in insert statement.
Now first try to copy this data from the table first to the second table.

After executing this, You will get an error for duplicate keys.

Now apply INSERT IGNORE Statement.

Result is :

MySQL Insert Ignore Statement

You can see the result where duplicate key has ignore by insert statement.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

  • Find duplicate records in MySQLSeptember 18, 2015 Find duplicate records in MySQL In this post, I demonstrate different way to find duplicate records in MySQL. I have found some alternative solution to find duplicate records […]
  • Insert-Update, Merge statement in MySQLAugust 18, 2015 Insert-Update, Merge statement in MySQL In this post, I discuss related to merge statement for Insert-Update of MySQL. For all the database developer merge statement is very popular […]
  • MySQL: Error Code-1175 You are using safe update modeOctober 31, 2015 MySQL: Error Code-1175 You are using safe update mode This is a very common error facing by MySQL Database Developer. Error Code:1175 You are using safe update mode. This means that you can't […]

Leave a Reply

1 Comment on "How to Insert if not exists in MySQL"

Notify of

Sort by:   newest | oldest | most voted
9 months 1 day ago

If some one desires expert view regarding blogging then i propose him/her to
pay a quick visit this web site, Keep up the fastidious job.