Autarchy of the Private Cave

• Categories

  Select Category*nixAffiliate programsArtificial Intelligence   Agents   Machine learning   Robotics   TechnologiesBooksComparisonHardwarehow-toHumourKyivLifeLinksMiscMoviesNotepadPersonalProgramming   Ada   OS   PHP   Python   XHTML/CSSqotdRantScience   Bioinformatics   Systems BiologySocietySoftware   SecurityStartupsUkraineWeb   CMS   Drupal   WP PlugInsWelfare

• Tags list

  blog btrfs bug CMS convert data database Debian development drupal example ffmpeg FireFox fix free gallery2 git google HDD hosting how-to http linux microarray module mysql package performance PHP plugin problem project management Python R repository server shared hosting spam statistics tool Ukraine update upgrade windows wordpress

• Related entries

  1. MySQL – Python: good MySQLdb tutorial (examples)
  2. Convert MySQL database from one encoding/collation into another
  3. How to improve MySQL application performance
  4. Search and replace in a MySQL table

• Subscribe

  

  Subscribe to RSS feed.

  Desktop Reader Bloglines Feedly Live Netvibes Yahoo!

• Archives

  Select Month December 2022 October 2022 October 2018 August 2018 July 2018 September 2017 August 2017 February 2017 December 2016 June 2016 May 2016 April 2016 March 2016 February 2016 January 2016 December 2015 July 2015 May 2015 April 2015 March 2015 February 2015 October 2014 September 2014 August 2014 April 2014 March 2014 January 2014 December 2013 November 2013 October 2013 August 2013 July 2013 June 2013 August 2012 June 2012 May 2012 February 2012 January 2012 December 2011 October 2011 August 2011 May 2011 April 2011 March 2011 February 2011 January 2011 December 2010 November 2010 October 2010 September 2010 August 2010 July 2010 June 2010 May 2010 April 2010 March 2010 February 2010 January 2010 December 2009 November 2009 October 2009 September 2009 August 2009 July 2009 June 2009 May 2009 April 2009 March 2009 February 2009 January 2009 December 2008 November 2008 October 2008 September 2008 August 2008 July 2008 June 2008 May 2008 April 2008 March 2008 February 2008 December 2007 November 2007 October 2007 September 2007 August 2007 July 2007 June 2007 May 2007 April 2007 March 2007 February 2007 January 2007 December 2006 November 2006 October 2006 September 2006 August 2006 July 2006 June 2006

• Recent comments

  • JT on How to truncate git history (sample script included)
  • Charlotte Semaan on How to fix: Xiaomi Mi Band stopped tracking steps and sleep
  • SAM on How to fix: Xiaomi Mi Band stopped tracking steps and sleep
  • SY on Convert MySQL database from one encoding/collation into another
  • Bogdan on Citation and reference managers for Linux: short overview

• Meta

  • Log in

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:

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:

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:

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.

This entry was posted on Thursday, October 18th, 2007 at 15:20 and is filed under Programming. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

46 Responses to “MySQL: INSERT IF NOT EXISTS syntax”

1. MYSQL On Duplicate Key Do Nothing OR Insert If Not Exists Says:
   April 20th, 2008 at 8:19

   [...] may also find http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html good reading on the [...]

2. ceteras Says:
   June 23rd, 2008 at 14:09

   Very interesting, and helpful.

3. Tautvydas Says:
   July 13th, 2008 at 12:53

   Thanks!

4. Emir Bugra KOKSALAN Says:
   July 22nd, 2008 at 12:38

   Thank you very much!!

5. Teo Says:
   August 4th, 2008 at 13:41

   Very useful, thank you! How about insert, update and delete in a single query, it’s possible? Something like:

   insert ignore into table (field_one, field_two) values (values_one, values_two) on duplicate key update field_one = values(values_one), field_two = values(values_two) THEN QUERY FOR DELETE HERE?

   Thanks

6. Bogdan Says:
   August 4th, 2008 at 19:25

   Teo,

   As I didn’t do anything similar before, I can’t offer you a ready working solution.

   But you should just try your query . The part before THEN should work (unless “insert ignore” is not compatible with “on duplicate key”). For THEN-part, I suspect different syntax might be needed – look for MySQL conditional statements IF-THEN-ELSE.

7. Geoffray Says:
   August 28th, 2008 at 14:39

   About your last option, I don’t think it’s possible to do nothing after the “ON DUPLICATE KEY UPDATE”. What you can do is updating the column (PRIMARY KEY or/and UNIQUE) which generate a duplicate entry error with it’s own value. I think MySQL will ignore the update because these values are similar, so it could also be a quite fast solution.

   INSERT INTO `test`(id) VALUES (1) ON DUPLICATE KEY UPDATE id=id

8. Bogdan Says:
   August 30th, 2008 at 16:21

   Geoffray,

   if the MySQL optimization engine really does that, and if this is allowed (remember, we are triggering the ON DUPLICATE KEY event, and then trying to assign that key’s value to another value, even if it is the same), then this is a really good solution.

   Based on your comment, I updated the post, as MySQL syntax in fact doesn’t allow the empty statement after ON (…) UPDATE.

9. Marius Says:
   September 29th, 2008 at 16:04

   Would this work on InnoDB with composite keys as well? My task is to read only new entries from a log and insert them into a MySQL table. The table has a composite key.

   (I could try this myself, but I have no MySQL box to test on for the next few weeks.)

10. Bogdan Says:
    September 29th, 2008 at 21:55

    Marius,

    neither MySQL documentation, nor my (not so extensive) experience with MySQL raise any support for disabling described functionality for composite keys. As I understand, composite key is still a single key, and behaves as such. And MySQL documentation sometimes mentions that composite keys are just concatenated respective fields (columns).

    Actually, it might be that I did apply the REPLACE or IGNORE approach to a composite-keyed table (and it was InnoDB at that time, now for that project I’m using MyISAM to save space). But I’m unsure – didn’t check the code since it was written (now almost a year ago).

11. cakirhal Says:
    October 29th, 2008 at 11:24

    thanks. it is very useful. Sometimes we want to send pop-up messagebox when we changed or ignord the same record. Your solutions are good but I want to know how to inform user that we changed or ignored the record. For example: “Customer couldn’t be added. Because the customer you entered is already exists.” So how can I determine whether users attempt is executed or ignored?

12. Bogdan Says:
    October 29th, 2008 at 13:01

    Cakirhal,

    first of all, you will need to consult MySQL documentation for the exact queries you are using – the “returned rows”/”affected rows” count might be helpful.

    Also, before executing the actual modification query, you can execute selection query (i.e. “SELECT … FROM”) in your program, to know for sure if the customer exists. After that, it is a trivial task to notify the user of any problems.

13. Todd Says:
    January 29th, 2009 at 16:41

    Thank you for the concise explanation.

14. Jacinto Says:
    June 24th, 2009 at 19:33

    thank you for your help, you are the best

15. wladek Says:
    June 24th, 2009 at 22:09

    Thanks, this article is very helpful!

16. dollan Says:
    August 5th, 2009 at 2:45

    I have a table A (col1 INT, col2 VARCHAR(1), col3 VARCHAR(1)) where col1 is primary key
    i’ve a record A(1, ‘A’,null)

    I want to update/insert a records if exists/not-exists to get a result of A(1, ‘A’, ‘B’);

    I can do this using update A set col2=’B’ where col1=1; But, I do not know whether col1=1 exists or not

    what shuld i do?

17. Bogdan Says:
    August 8th, 2009 at 16:28

    Dollan, have you actually read the post text? Either I do not understand your problem, or it can indeed be solved using either INSERT IGNORE or REPLACE INTO.

18. IK Says:
    September 23rd, 2009 at 20:52

    Great Post, thanks!
    I am thinking that INSERT IGNORE would not overwrite like REPLACE would, should one want to insert the same text with different parameters. I will definitely try that now.

    Regards

19. Lyncis Says:
    October 26th, 2009 at 1:31

    Thanks for good explanation.

20. Phil Says:
    November 7th, 2009 at 2:28

    Thanks! Glad to know I’m not the only one thinking this way.

21. MySQL: insert ถ้ายังไม่มี « वीर | บันทึกของวีร์ | Vee(r)'s Blog Says:
    November 27th, 2009 at 20:50

    [...] ง่ายๆ เท่าที่อ่านมาจาก http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html ผ่านทาง [...]

22. willieray Says:
    December 1st, 2009 at 8:16

    Fantastic!!! you saved my frazzled brain. if not exists was tormenting me.

23. Eddie Says:
    March 9th, 2010 at 17:07

    I did some speed benchmarking of these methods and found that ON DUPLICATE KEY is the faster that INSERT IGNORE. I also found that just letting the query fail because of a primary key constraint is faster than both. For my application, anyway. Your mileage may vary. More info in my “website” link.

24. Bogdan Says:
    March 9th, 2010 at 17:46

    Thanks, that was an interesting comparison. I only had tens of thousands of rows, so didn’t pay attention to performance.

25. Alex Says:
    April 22nd, 2010 at 8:53

    
REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;


    It’s important to note that unless you mention all fields and have autogenerated fields (such as autoincremented primary key), you get to screw up your DB with this method:

    I tried
    
REPLACE INTO images SET filename='variable'


    and guess what, since a record existed, its autoincremened ID got changed.
    So watch out for such mistakes: my error may be your lesson.

26. Server Says:
    June 11th, 2010 at 20:29

    Also i’m using

    INSERT INTO `table` (value1,value2) SELECT ‘stuff for value1′,’stuff for value2′ WHERE NOT EXISTS (SELECT * FROM table WHERE value1=’stuff for value1′ AND value2=’stuff for value2′) LIMIT 1

27. Bogdan Says:
    June 12th, 2010 at 1:24

    Using sub-query most likely will be slower than all the other solutions (because sub-queries will use temporary tables).

    Also, MySQL INSERT…SELECT manual page says: However, you cannot insert into a table and select from the same table in a subquery. Thus, your solution may not work at all.

28. cutesmyls Says:
    July 16th, 2010 at 8:12

    Hi!

    I have a note table, each note has a respective x and y positions..and everytime i add/create a new note, the newly added note should have a unique x and y values in the db..can you suggest on how to do this on sql?

29. Bogdan Says:
    July 16th, 2010 at 14:04

    MySQL-specific solution would be to use autoincrement (linked page also has an example for a two-component primary key, something like your X and Y; see also comments there for more detailed explanation).

    Actually, do think if you really want those X and Y serve the purpose of unique IDs… maybe it is better having a single ID, and storing X and Y as values?

    In any case, you could implement X/Y-incrementing logic in your app completely DB-engine independently, e.g.
    
X.new = SELECT MAX(X) + 1 FROM table;
Y.new = SELECT MAX(Y) + 1 FROM table;
INSERT INTO table(X, Y, note) VALUES (X.new, Y.new, note);

    However, in this case you should be aware of possible race conditions, if there are several processes possibly writing to that table. To avoid that, you will need table locking. Some apps (e.g. Gallery2) maintain separate tables (sequences) of incremental IDs, to avoid locking data-tables in favour of locking only sequence-tables…

    Better use a single autoincrement ID

30. ienaxxx Says:
    September 8th, 2010 at 17:19

    Very nice article, really useful

31. ViserExcizer Says:
    September 19th, 2010 at 6:42

    wundervoll! thanks for this

32. vivin Says:
    September 30th, 2010 at 7:17

    nice thank u!!!

33. Claude Quézel Says:
    February 18th, 2011 at 5:54

    Note that the mysql doc states:

    If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

    INSERT INTO table (a,b,c) VALUES (1,2,3)
    ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

34. Thanks Says:
    August 29th, 2011 at 6:22

    Thanks man.. first result on google, no need to go anywhere else

35. Chris Says:
    September 9th, 2011 at 19:29

    cheers – just saved me a real headache. Was considering doing a SELECT for every record, checking for existence, then making a decision based on that… man, sometimes I go round the houses when a simple solution is right there in front of me.

36. Pope Says:
    September 20th, 2011 at 19:37

    You are my Hero!

37. MySQL if exist update else insert | prosoxi.com Says:
    September 29th, 2011 at 13:40

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

38. Bill Says:
    October 14th, 2011 at 15:19

    I have created a page that will automatically add posts to WordPress but every time it runs, it creates duplicate posts. The code string is below. I’ve tried several variations to get the code to update the record if it has changed, otherwise ignore it and add any new records. Any help would be appreciated.

    $link = mysql_connect($hostname, $user, $pass);
    mysql_select_db($dbname, $link);

    $results = mysql_query(“SELECT * FROM genesis”,$link);

    require(‘./wp-load.php’);

39. Frank Says:
    November 21st, 2011 at 13:23

    Thanks for info:INSERT IGNORE INTO table_name SET col = ‘value’;
    Still, table shall be updated as:
    ALTER TABLE `db_name`.`table_name` ADD UNIQUE KEY(col);
    (otherwise, the first statement won’t work.)
    All the best,

40. Subhendu Says:
    November 29th, 2011 at 21:03

    Thanks dude..

    After struggling for couple of hours I found this “INSERT IGNORE INTO”.

    Thanks A TON

41. KKR Says:
    December 16th, 2011 at 22:35

    INSERT into Foo.bar select “va11″, “val2″ from dual where not exists (select val1 from Foo.bar where username = “val1″);

42. Mario Says:
    January 3rd, 2012 at 12:06

    “Insert Ignore into” increments auto number even in cases that new record is not added.

43. MySQL INSERT IF NOT EXISTS: Checking whether a record exists and inserting » What Is Internet? Says:
    March 30th, 2013 at 11:21

    [...] Bogdan has the answers . [...]

44. Sabby Says:
    August 31st, 2013 at 9:09

    Very useful and best information, only thing is that site fonts are very small. We cannot read it. INSERT IGNORE INTO `transcripts`
    SET `ensembl_transcript_id` = ‘ENSORGT00000000001′,
    `transcript_chrom_start` = 12345,
    `transcript_chrom_end` = 12678;
    I hope you understand what I mean, ignore my comment just make the changes.
    Thank you.

45. Bogdan Says:
    August 31st, 2013 at 16:07

    I’ve increased font size a little, should be better now. What is your screen resolution? Is it higher than 1920×1080 or 1920×1200?

46. ‘insert if not exists’ in MySQL | Coding and Programing Says:
    June 3rd, 2014 at 5:47

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

Leave a Reply