{"id":1073,"date":"2013-09-13T07:48:08","date_gmt":"2013-09-13T07:48:08","guid":{"rendered":"https:\/\/ibex.tech\/cloud\/?p=1073"},"modified":"2022-02-17T07:14:03","modified_gmt":"2022-02-17T07:14:03","slug":"insert-or-update-on-duplicate","status":"publish","type":"post","link":"https:\/\/ibex.tech\/cloud\/mysql\/queries\/update-queries\/insert-or-update-on-duplicate","title":{"rendered":"INSERT or UPDATE ON DUPLICATE"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\">Object Oriented Style<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\">Insert Or Update If Already Present<\/h5>\n\n\n\n<p><em>(Update if primary keys are not unique)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>  $stmt = $maindb->prepare(\"\n    INSERT INTO my_table\n    (\n      MyColumn1,\n      MyColumn2\n    ) VALUES (\n      ?,\n      ?\n    )\n    ON DUPLICATE KEY UPDATE\n      MyColumn1 = ?,\n      MyColumn2 = ?\n    \");\n  $stmt->bind_param(\"sisi\", $MyColumn1, $MyColumn2, $MyColumn1, $MyColumn2);\n  $MyColumn1 = \"abc\";\n  $MyColumn2 = 12;\n  $stmt->execute();\n  $stmt->close();<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">Insert Or Update If Already Present WHERE<\/h5>\n\n\n\n<p>You can &#8216;t use WHERE with ON DUPLICATE KEY UPDATE.  A typical solution is to use a double operation instead, INSERT IGNORE followed by and UPDATE WHERE<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">Insert or Do Nothing If Already Present<\/h5>\n\n\n\n<p><em>(Checks to see if primary keys are unique)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>  $stmt = $maindb->prepare(\"\n    INSERT IGNORE INTO my_table\n    (\n      MyColumn1,\n      MyColumn2\n    ) VALUES (\n      ?,\n      ?\n    )\");\n  $stmt->bind_param(\"si\", $MyColumn1, $MyColumn2);\n  $MyColumn1 = \"abc\";\n  $MyColumn2 = 12;\n  $stmt->execute();\n  $AffectedRows = $stmt->affected_rows;\n  $stmt->close();<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">ON DUPLICATE KEY DELETE FROM<\/h4>\n\n\n\n<p>No you can&#8217;t do a delete!<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Procedural&nbsp;Style &#8211; Old PHP4 Code<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\">Insert Or Update If Already Present<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>$result = @mysql_query(\"\n\tINSERT INTO my_table (\n\t\tsome_int,\n\t\tsome_string\n\t) VALUES (\n\t\t$my_int,\n\t\t'$my_string'\n\t)\n\tON DUPLICATE KEY UPDATE\n\t\tsome_int = $my_int,\n\t\tsome_string = '$my_string'\n\t\");<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">Insert or Do Nothing If Already Present<\/h5>\n\n\n\n<p>This simply tests to see if primary keys are unique:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$result = @mysql_query(\"\n\tINSERT IGNORE INTO my_table (\n\t\tsome_int,\n\t\tsome_string\n\t) VALUES (\n\t\t$my_int,\n\t\t'$my_string'\n\t)\");<\/code><\/pre>\n\n\n\n<p>If you have an auto column (e.g. a primary index that&#8217;s just a record ID say) then the above won&#8217;t work because that column will have a new value and so the new row is not a duplicate &#8211; result is a new row is added. &nbsp;To solve this you need to add a UNIQUE index to the table. &nbsp;A unique index can have multiple columns creating it so you simply&nbsp;add the columns you want to never be duplicated to this. &nbsp;MySQL tools will typically let you do this &#8211; look for &#8216;Indexes&#8217; and then for one you can create with multiple columns and that allows its type to be set to unique.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Object Oriented Style Insert Or Update If Already Present (Update if primary keys are not unique) Insert Or Update If Already Present WHERE You can &#8216;t use WHERE with ON DUPLICATE KEY UPDATE. A typical solution is to use a double operation instead, INSERT IGNORE followed by and UPDATE WHERE Insert or Do Nothing If [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[262,240],"tags":[],"class_list":["post-1073","post","type-post","status-publish","format-standard","hentry","category-insert-queries","category-update-queries"],"_links":{"self":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts\/1073","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/comments?post=1073"}],"version-history":[{"count":16,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts\/1073\/revisions"}],"predecessor-version":[{"id":3093,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts\/1073\/revisions\/3093"}],"wp:attachment":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/media?parent=1073"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/categories?post=1073"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/tags?post=1073"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}