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.

MySQL INSERT IGNORE statement:

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.

Anvesh Patel

Leave a Reply

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

Notify of
avatar
Sort by:   newest | oldest | most voted
wick
Guest

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.

wpDiscuz