{"id":655,"date":"2016-01-06T15:53:53","date_gmt":"2016-01-06T14:53:53","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=655"},"modified":"2016-01-07T10:15:05","modified_gmt":"2016-01-07T09:15:05","slug":"how-an-oracle-error-can-send-you-the-wrong-way%e2%80%a6","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=655","title":{"rendered":"How an Oracle error can send you the wrong way\u2026"},"content":{"rendered":"<style type=\"text\/css\">\n.code, .code pre\n{\n  font-size: small;\n  color: black;\n  font-family: consolas, \"Courier New\", courier, monospace;\n  background-color: #ffffff;\n  \/*white-space: pre;*\/\n}\n.code pre { margin: 0em; }\n.code .rem { color: #ff0000; }\n.code .kwrd { color: #008080; }\n.code .str { color: #0000ff; }\n.code .op { color: #0000c0; }\n.code .preproc { color: #cc6633; }\n.code .asp { background-color: #ffff00; }\n.code .html { color: #800000; }\n.code .attr { color: #ff0000; }\n.code .alt\n{\n  background-color: #f4f4f4;\n  width: 100%;\n  margin: 0em;\n}\n.code .lnum { color: #606060; }<\/style>\n<p>At my current assignment I needed to create an update statement to copy data from one table to another. Quite a simple task, I would say, but an error or actually a constraint violation sent me the wrong way in finding my solution.<\/p>\n<p><!--more--><\/p>\n<p>Suppose I have two tables:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">TABLE<\/span> a\r\n( mykey <span class=\"kwrd\">NUMBER<\/span>\r\n, thisvalue <span class=\"kwrd\">VARCHAR2<\/span>(<span class=\"str\">20<\/span>) <span class=\"kwrd\">NOT<\/span> <span class=\"kwrd\">NULL<\/span>\r\n)\r\n\/<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">TABLE<\/span> b\r\n( mykey <span class=\"kwrd\">NUMBER<\/span>\r\n, thatvalue <span class=\"kwrd\">VARCHAR2<\/span>(<span class=\"str\">20<\/span>) <span class=\"kwrd\">NOT<\/span> <span class=\"kwrd\">NULL<\/span>\r\n)\r\n\/<\/pre>\n<\/div>\n<p>and some data in them: <\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> a (mykey,thisvalue) <span class=\"kwrd\">VALUES<\/span> (1,'<span class=\"str\">Larry<\/span>');\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> a (mykey,thisvalue) <span class=\"kwrd\">VALUES<\/span> (2,'<span class=\"str\">Bryn<\/span>');\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> a (mykey,thisvalue) <span class=\"kwrd\">VALUES<\/span> (3,'<span class=\"str\">Steven<\/span>');\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> a (mykey,thisvalue) <span class=\"kwrd\">VALUES<\/span> (4,'<span class=\"str\">Patrick<\/span>');\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> b (mykey,thatvalue) <span class=\"kwrd\">VALUES<\/span> (1,'<span class=\"str\">Larry Ellison<\/span>');\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> b (mykey,thatvalue) <span class=\"kwrd\">VALUES<\/span> (2,'<span class=\"str\">Bryn Llewellyn<\/span>');\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> b (mykey,thatvalue) <span class=\"kwrd\">VALUES<\/span> (3,'<span class=\"str\">Steven Feuerstein<\/span>');\r\n<span class=\"kwrd\">COMMIT<\/span>\r\n\/ <\/pre>\n<\/div>\n<p>Now I want to update the values in table a with the values of table b. My first idea was to write a statement like this:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">UPDATE<\/span> a\r\n   <span class=\"kwrd\">SET<\/span> a.thisvalue = (select b.thatvalue\r\n                        <span class=\"kwrd\">FROM<\/span> b\r\n                       <span class=\"kwrd\">WHERE<\/span> b.mykey = a.mykey)\r\n\/<\/pre>\n<\/div>\n<p>but this statement led to the following error:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"rem\">ORA-01407: cannot update (&quot;DEMO&quot;.&quot;A&quot;.&quot;THISVALUE&quot;) to NULL <\/span><\/pre>\n<\/div>\n<p>\n  <br \/>No problem, I thought, if the new value is somehow NULL, then just use the old value:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">UPDATE<\/span> a\r\n   <span class=\"kwrd\">SET<\/span> a.thisvalue = (select <span class=\"kwrd\">NVL<\/span>(b.thatvalue, a.thisvalue)\r\n                        <span class=\"kwrd\">FROM<\/span> b\r\n                       <span class=\"kwrd\">WHERE<\/span> b.mykey = a.mykey)\r\n\/<\/pre>\n<\/div>\n<p>but this still resulted in the same error:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"rem\">ORA-01407: cannot update (&quot;DEMO&quot;.&quot;A&quot;.&quot;THISVALUE&quot;) to NULL <\/span><\/pre>\n<\/div>\n<p>Then it dawned upon me. For mykey=4 there would be no match in table B, which resulted in no row returned, hence a NULL value.<\/p>\n<p>The error given is absolutely correct, but it sent me the wrong way in finding a solution. If I would have gotten a NO DATA FOUND error, I would have known right away what was the problem.<\/p>\n<p>The solution was pretty easy, just update the rows that have a matching row in table B:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">UPDATE<\/span> a\r\n   <span class=\"kwrd\">SET<\/span> a.thisvalue = (select b.thatvalue\r\n                        <span class=\"kwrd\">FROM<\/span> b\r\n                       <span class=\"kwrd\">WHERE<\/span> b.mykey = a.mykey)\r\n <span class=\"kwrd\">WHERE<\/span> <span class=\"kwrd\">EXISTS<\/span> (select b.thatvalue\r\n                 <span class=\"kwrd\">FROM<\/span> b\r\n                <span class=\"kwrd\">WHERE<\/span> b.mykey = a.mykey)\r\n\/<\/pre>\n<\/div>\n<p>Another solution might be using the MERGE statement:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">MERGE<\/span> <span class=\"kwrd\">INTO<\/span> a\r\n<span class=\"kwrd\">USING<\/span> (select b.mykey, b.thatvalue\r\n         <span class=\"kwrd\">FROM<\/span> b) b\r\n<span class=\"kwrd\">ON<\/span> (a.mykey = b.mykey)\r\n<span class=\"kwrd\">WHEN<\/span> <span class=\"kwrd\">MATCHED<\/span> <span class=\"kwrd\">THEN<\/span>\r\n  <span class=\"kwrd\">UPDATE<\/span>\r\n     <span class=\"kwrd\">SET<\/span> a.thisvalue = b.thatvalue\r\n\/<\/pre>\n<\/div>\n<p>If the subselect results in more than one row you get an equivalent of the TOO_MANY_ROWS exception, but if the subselect results in no rows you don\u2019t get the NO_DATA_FOUND (or equivalent) exception.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>At my current assignment I needed to create an update statement to copy data from one table to another. Quite a simple task, I would say, but an error or actually a constraint violation sent me the wrong way in finding my solution.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,9],"tags":[],"class_list":["post-655","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/655","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=655"}],"version-history":[{"count":6,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/655\/revisions"}],"predecessor-version":[{"id":661,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/655\/revisions\/661"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=655"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=655"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=655"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}