MySQL: INSERT IF NOT EXISTS syntax


To start: as of the latest MySQL, syntax presented in the title is not possible. But there are several very easy ways to accomplish what is expected using existing functionality.

There are 3 possible solutions: using INSERT IGNORE, REPLACE, or INSERT … ON DUPLICATE KEY UPDATE.

Imagine we have a table:

SQL:

  1. CREATE TABLE `transcripts` (
  2.  `ensembl_transcript_id` varchar(20) NOT NULL,
  3.  `transcript_chrom_start` int(10) UNSIGNED NOT NULL,
  4.  `transcript_chrom_end` int(10) UNSIGNED NOT NULL,
  5.  PRIMARY KEY  (`ensembl_transcript_id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now imagine that we have an automatic pipeline importing transcripts meta-data from Ensembl, and that due to various reasons the pipeline might be broken at any step of execution. Thus, we need to ensure two things: 1) repeated executions of the pipeline will not destroy our database, and 2) repeated executions will not die due to ‘duplicate primary key’ errors.

Method 1: using REPLACE

It’s very simple:

SQL:

  1. REPLACE INTO `transcripts`
  2. SET `ensembl_transcript_id` = ‘ENSORGT00000000001’,
  3. `transcript_chrom_start` = 12345,
  4. `transcript_chrom_end` = 12678;

If the record exists, it will be overwritten; if it does not yet exist, it will be created.
However, using this method isn’t efficient for our case: we do not need to overwrite existing records, it’s fine just to skip them.

Method 2: using INSERT IGNORE
Also very simple:

SQL:

  1. INSERT IGNORE INTO `transcripts`
  2. SET `ensembl_transcript_id` = ‘ENSORGT00000000001’,
  3. `transcript_chrom_start` = 12345,
  4. `transcript_chrom_end` = 12678;

Here, if the ‘ensembl_transcript_id’ is already present in the database, it will be silently skipped (ignored). (To be more precise, here’s a quote from MySQL reference manual: “If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted.”.) If the record doesn’t yet exist, it will be created.

This second method has several potential weaknesses, including non-abortion of the query in case any other problem occurs (see the manual). Thus it should be used if previously tested without the IGNORE keyword.

There is one more option: to use INSERT … ON DUPLICATE KEY UPDATE syntax, and in the UPDATE part just do nothing do some meaningless (empty) operation, like calculating 0+0 (Geoffray suggests doing the id=id assignment for the MySQL optimization engine to ignore this operation). Advantage of this method is that it only ignores duplicate key events, and still aborts on other errors.

As a final notice: this post was inspired by Xaprb. I’d also advise to consult his other post on writing flexible SQL queries.

http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s