How to write INSERT IF NOT EXISTS queries in standard SQL<\/p>\n
If necessary, INSERT IF NOT EXISTS queries can be written in a single atomic statement, eliminating the need for a transaction, and without violating standards.
\nIn this article we explained several ways to write such queries in a platform-independent way.<\/p>\n
Several solutions:<\/strong><\/span><\/p>\n One solution is to use a\u00a0mutex table<\/p>\n[pastacode lang=\u201dsql\u201d manual=\u201dinsert%20into%20urls(url)%0Aselect%20\u2019%2Fblog%2F\u2019%0Afrom%20mutex%0A%20%20%20%20left%20outer%20join%20urls%0A%20%20%20%20%20%20%20%20on%20urls.url%20%3D%20\u2019%2Fblog%2F\u2019%0Awhere%20mutex.i%20%3D%201%20and%20urls.url%20is%20null%3B%0A\u201d message=\u201dSql Code\u201d highlight=\u201d\u201d provider=\u201dmanual\u201d\/]\n There are more flexible variations on this technique. Suppose there is no unique index on the url column. <\/strong><\/p>\n If desired, it is possible to insert several values in a single statement by changing the\u00a0WHERE\u00a0clause:<\/strong><\/span><\/p>\n[pastacode lang=\u201dsql\u201d manual=\u201dinsert%20into%20urls(url)%0Aselect%20\u2019%2Fblog%2F\u2019%0Afrom%20mutex%0A%20%20%20%20left%20outer%20join%20urls%0A%20%20%20%20%20%20%20%20on%20urls.url%20%3D%20\u2019%2Fblog%2F\u2019%0Awhere%20mutex.i%20%3C%205%20and%20urls.url%20is%20null%3B%0A\u201d message=\u201dSql Code\u201d highlight=\u201d\u201d provider=\u201dmanual\u201d\/]\n[ad type=\u201dbanner\u201d]\n Now suppose the requirements specify up to three duplicate entries in the table, and each insert should add a single row. <\/strong><\/p>\n It is possible to insert a row at a time while enforcing the requirement with the following query:<\/strong><\/span><\/p>\n[pastacode lang=\u201dsql\u201d manual=\u201dinsert%20into%20urls(url)%0Aselect%20\u2019%2Fblog%2F\u2019%0Afrom%20mutex%0A%20%20%20%20left%20outer%20join%20urls%0A%20%20%20%20%20%20%20%20on%20urls.url%20%3D%20\u2019%2Fblog%2F\u2019%0Awhere%20mutex.i%20%3D%201%0Agroup%20by%20urls.url%0Ahaving%20count(*)%20%3C%203%3B%0A\u201d message=\u201dSql Code\u201d highlight=\u201d\u201d provider=\u201dmanual\u201d\/]\n This query shows the input to the\u00a0INSERT\u00a0statement:<\/strong><\/span><\/p>\n[pastacode lang=\u201dsql\u201d manual=\u201dselect%20\u2019%2Fblog%2F\u2019%2C%20count(*)%0Afrom%20mutex%0A%20%20%20%20left%20outer%20join%20urls%0A%20%20%20%20%20%20%20%20on%20urls.url%20%3D%20\u2019%2Fblog%2F\u2019%0Awhere%20mutex.i%20%3D%201%0Agroup%20by%20urls.url%3B%0A\u201d message=\u201dSql Code\u201d highlight=\u201d\u201d provider=\u201dmanual\u201d\/]\n 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.<\/p>\n Another 3 possible solutions:<\/strong><\/span> Below we\u2019ll examine the three different methods and explain the pros and cons of each in turn.<\/p>\n so you have a firm grasp on how to configure your own statements when providing new or potentially existing data for INSERTION.<\/p>\n Using INSERT IGNORE:<\/strong><\/span><\/p>\n Using INSERT IGNORE effectively causes MySQL to ignore execution errors while attempting to perform INSERT statements.<\/p>\n This means that an INSERT IGNORE statement which contains a duplicate value in a UNIQUE index or PRIMARY KEY field does not produce an error, but will instead simply ignore that particular INSERT command entirely.<\/p>\n The obvious purpose is to execute a large number of INSERT statements for a combination of data that is both already existing in the database as well as new data coming into the system.<\/p>\n For example, our books table might contain a few records already:<\/strong><\/span><\/p>\n mysql> SELECT * FROM books LIMIT 3;<\/p>\n +\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-<\/p>\n | id | title\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | author\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 year_published |<\/p>\n +\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014<\/p>\n |\u00a0 1 | In Search of Lost Time \u00a0 | Marcel Proust \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1913\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/p>\n |\u00a0 2 | Ulysses \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| James Joyce \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1922\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/p>\n |\u00a0 3 | Don Quixote \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| Miguel de Cervantes \u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1605\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/p>\n +\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-<\/p>\n 3 rows in set (0.00 sec)<\/p>\n If we have a large batch of new and existing data to INSERT and part of that data contains a matching value for the id field (which is a UNIQUE PRIMARY_KEY in the table), using a basic INSERT will produce an expected error:<\/strong><\/p>\n[pastacode lang=\u201dsql\u201d manual=\u201dmysql%3E%20INSERT%20INTO%20books%0A%20%20%20%20(id%2C%20title%2C%20author%2C%20year_published)%0AVALUES%0A%20%20%20%20(1%2C%20\u2019Green%20Eggs%20and%20Ham\u2019%2C%20\u2019Dr.%20Seuss\u2019%2C%201960)%3B%0AERROR%201062%20(23000)%3A%20Duplicate%20entry%20\u20191\u2019%20for%20key%20\u2019PRIMARY\u2019%0A\u201d message=\u201dSql Code\u201d highlight=\u201d\u201d provider=\u201dmanual\u201d\/]\n[ad type=\u201dbanner\u201d]\n On the other hand, if we use INSERT IGNORE, the duplication attempt is ignored and no resulting errors occur:<\/strong><\/p>\n[pastacode lang=\u201dsql\u201d manual=\u201dmysql%3E%20INSERT%20IGNORE%20INTO%20books%0A%20%20%20%20(id%2C%20title%2C%20author%2C%20year_published)%0AVALUES%0A%20%20%20%20(1%2C%20\u2019Green%20Eggs%20and%20Ham\u2019%2C%20\u2019Dr.%20Seuss\u2019%2C%201960)%3B%0AQuery%20OK%2C%200%20rows%20affected%20(0.00%20sec)%0A\u201d message=\u201dSql Code\u201d highlight=\u201d\u201d provider=\u201dmanual\u201d\/]\n Using REPLACE<\/p>\n In the event that you wish to actually replace rows where INSERT commands would produce errors due to duplicate UNIQUE or PRIMARY KEY values as outlined above, one option is to opt for the REPLACE statement.<\/p>\n When issuing a REPLACE statement, there are two possible outcomes for each issued command:<\/strong><\/p>\n No existing data row is found with matching values and thus a standard INSERT statement is performed.<\/p>\n A matching data row is found, causing that existing row to be deleted with the standard DELETE statement, then a normal INSERT is performed afterward.<\/p>\n For example,<\/strong> Notice that even though we only altered one row, the result indicates that two rows were affected because, we actually DELETED the existing row then INSERTED the new row to replace it.<\/p>\n Using INSERT \u2026 ON DUPLICATE KEY UPDATE<\/p>\n The alternative (and generally preferred) method for INSERTING into rows that may contain duplicate UNIQUE or PRIMARY KEY values is to use the INSERT \u2026 ON DUPLICATE KEY UPDATE statement and clause.<\/p>\n Unlike REPLACE \u2013 an inherently destructive command due to the DELETE commands it performs when necessary \u2013 using INSERT \u2026 ON DUPLICATE KEY UPDATE is non-destructive, in that it will only ever issue INSERT or UPDATE statements, but never DELETE.<\/p>\n For example,<\/strong> We can therefore take our original INSERT statement and add the new ON DUPLICATE KEY UPDATE clause:<\/strong><\/p>\n[pastacode lang=\u201dsql\u201d manual=\u201dmysql%3E%20SET%20%40id%20%3D%201%2C%0A%20%20%20%20%40title%20%3D%20\u2019In%20Search%20of%20Lost%20Time\u2019%2C%0A%20%20%20%20%40author%20%3D%20\u2019Marcel%20Proust\u2019%2C%0A%20%20%20%20%40year_published%20%3D%201913%3B%0AINSERT%20INTO%20books%0A%20%20%20%20(id%2C%20title%2C%20author%2C%20year_published)%0AVALUES%0A%20%20%20%20(%40id%2C%20%40title%2C%20%40author%2C%20%40year_published)%0AON%20DUPLICATE%20KEY%20UPDATE%0A%20%20%20%20title%20%3D%20%40title%2C%0A%20%20%20%20author%20%3D%20%40author%2C%0A%20%20%20%20year_published%20%3D%20%40year_published%3B%0A\u201d message=\u201dSql Code\u201d highlight=\u201d\u201d provider=\u201dmanual\u201d\/]\n Notice that we\u2019re using normal UPDATE syntax (but excluding the unnecessary table name and SET keyword), and only assigning the non-UNIQUE values.<\/p>\n Also, although unnecessary for the ON DUPLICATE KEY UPDATE method to function properly, we\u2019ve also opted to utilize user variables so we don\u2019t need to specify the actual values we want to INSERT or UPDATE more than once.<\/p>\n As a result, our id = 1 record was properly UPDATED as expected:<\/strong><\/p>\n mysql> SELECT * FROM books LIMIT 1;<\/p>\n +\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014\u2014\u2014\u2014\u2014+<\/p>\n | id | title\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | author\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | year_published |<\/p>\n +\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014\u2014-+<\/p>\n |\u00a0 1 | In Search of Lost Time\u00a0\u00a0 | Marcel Proust |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1913\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/p>\n +\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+<\/p>\n 1 row in set (0.00 sec)<\/p>\n","protected":false},"excerpt":{"rendered":" How to write INSERT IF NOT EXISTS queries in standard SQL If necessary, INSERT IF NOT EXISTS queries can be written in a single atomic statement, eliminating the need for a transaction, and without violating standards. In this article we explained several ways to write such queries in a platform-independent way. Several solutions: One solution […]<\/p>\n","protected":false},"author":2,"featured_media":1205,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27,28],"tags":[1762,1769,1767,1763,1770,1766,1764,1765,1768],"class_list":["post-938","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","category-sql-server","tag-how-to-insert-if-not-exists-in-mysql","tag-insert-if-not-exists-oracle","tag-insert-if-not-exists-postgresql","tag-insert-if-not-exists-sql-server","tag-insert-if-not-exists-sqlite","tag-mysql-insert-if-not-exists-else-do-nothing","tag-mysql-insert-if-not-exists-else-update","tag-mysql-insert-ignore","tag-mysql-where-not-exists"],"_links":{"self":[{"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/posts\/938","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/comments?post=938"}],"version-history":[{"count":0,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/posts\/938\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/media\/1205"}],"wp:attachment":[{"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/media?parent=938"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/categories?post=938"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/tags?post=938"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nINSERT IGNORE,
\nREPLACE,
\nINSERT \u2026 ON DUPLICATE KEY UPDATE.<\/p>\n
\nwe can use REPLACE to swap out our existing record of id = 1 of In Search of Lost Time by Marcel Proust with Green Eggs and Ham by Dr. Seuss:<\/strong><\/p>\n[pastacode lang=\u201dsql\u201d manual=\u201dmysql%3E%20REPLACE%20INTO%20books%0A%20%20%20%20(id%2C%20title%2C%20author%2C%20year_published)%0AVALUES%0A%20%20%20%20(1%2C%20\u2019Green%20Eggs%20and%20Ham\u2019%2C%20\u2019Dr.%20Seuss\u2019%2C%201960)%3B%0AQuery%20OK%2C%202%20rows%20affected%20(0.00%20sec)%0A\u201d message=\u201dSql Code\u201d highlight=\u201d\u201d provider=\u201dmanual\u201d\/]\n
\nwe have decided we wish to replace our id = 1 record of Green Eggs and Ham and revert it back to the original In Search of Lost Time record instead.<\/p>\n