MySQL: INSERT IF NOT EXISTS syntax
18th October 2007
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:
- CREATE TABLE `transcripts` (
- `ensembl_transcript_id` varchar(20) NOT NULL,
- `transcript_chrom_start` int(10) unsigned NOT NULL,
- `transcript_chrom_end` int(10) unsigned NOT NULL,
- PRIMARY KEY (`ensembl_transcript_id`)
- ) 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
Read the rest of this entry »
Posted in Programming | 46 Comments »