{"id":647,"date":"2015-12-23T12:03:39","date_gmt":"2015-12-23T11:03:39","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=647"},"modified":"2015-12-25T08:49:45","modified_gmt":"2015-12-25T07:49:45","slug":"update-multiple-columns","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=647","title":{"rendered":"Update multiple columns"},"content":{"rendered":"<style type=\"text\/css\">\n<p>.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>This is something I knew somewhere in the back of my head, but had forgotten about until now.<\/p>\n<p>When you want to update multiple columns in a single SQL statement based on a sub query you can of course duplicate this query for every column you want to update. But this violates the SPOD (Single Point Of Definition) \u2018rule\u2019.<\/p>\n<p><!--more--><\/p>\n<p>As an example I have the following requirement:<\/p>\n<p>Add two columns to the EMP table containing the name and job of the manager and fill these columns with the right values.<\/p>\n<p>First of all I need to add the columns to the table, with is easy:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">alter<\/span> <span class=\"kwrd\">table<\/span> emp <span class=\"kwrd\">add<\/span> (mgr_name <span class=\"kwrd\">varchar2<\/span>(<span class=\"attr\">10<\/span>)\r\n                    ,mgr_job <span class=\"kwrd\">varchar2<\/span>(<span class=\"attr\">9<\/span>)\r\n                    )<\/pre>\n<\/div>\n<p>Then comes the \u2018tricky\u2019 part. I can of course fill up these columns in separate statements, like this:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">update<\/span> emp e\r\n   <span class=\"kwrd\">set<\/span> e.mgr_name = (<span class=\"kwrd\">select<\/span> m.ename\r\n                       <span class=\"kwrd\">from<\/span> emp m\r\n                      <span class=\"kwrd\">where<\/span> 1=1\r\n                        <span class=\"kwrd\">and<\/span> m.empno = e.mgr)\r\n <span class=\"kwrd\">where<\/span> 1=1\r\n   <span class=\"kwrd\">and<\/span> e.mgr <span class=\"kwrd\">is<\/span> <span class=\"kwrd\">not<\/span> <span class=\"kwrd\">null<\/span>\r\n\/\r\n<span class=\"kwrd\">update<\/span> emp e\r\n   <span class=\"kwrd\">set<\/span> e.mgr_job = (<span class=\"kwrd\">select<\/span> m.job\r\n                       <span class=\"kwrd\">from<\/span> emp m\r\n                      <span class=\"kwrd\">where<\/span> 1=1\r\n                        <span class=\"kwrd\">and<\/span> m.empno = e.mgr)\r\n <span class=\"kwrd\">where<\/span> 1=1\r\n   <span class=\"kwrd\">and<\/span> e.mgr <span class=\"kwrd\">is<\/span> <span class=\"kwrd\">not<\/span> <span class=\"kwrd\">null<\/span>\r\n\/<\/pre>\n<\/div>\n<p>But this implies two roundtrips from the client to the database. These statements can be combined into a single one:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">update<\/span> emp e\r\n   <span class=\"kwrd\">set<\/span> e.mgr_name = (<span class=\"kwrd\">select<\/span> m.ename\r\n                       <span class=\"kwrd\">from<\/span> emp m\r\n                      <span class=\"kwrd\">where<\/span> 1=1\r\n                        <span class=\"kwrd\">and<\/span> m.empno = e.mgr)\r\n     , e.mgr_job = (<span class=\"kwrd\">select<\/span> m.job\r\n                       <span class=\"kwrd\">from<\/span> emp m\r\n                      <span class=\"kwrd\">where<\/span> 1=1\r\n                        <span class=\"kwrd\">and<\/span> m.empno = e.mgr)\r\n <span class=\"kwrd\">where<\/span> 1=1\r\n   <span class=\"kwrd\">and<\/span> e.mgr <span class=\"kwrd\">is<\/span> <span class=\"kwrd\">not<\/span> <span class=\"kwrd\">null<\/span>\r\n\/<\/pre>\n<\/div>\n<p>But there is an easier, and more elegant way to do this:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">update<\/span> emp e\r\n   <span class=\"kwrd\">set<\/span> (e.mgr_name, e.mgr_job) = (<span class=\"kwrd\">select<\/span> m.ename, m.job\r\n                                   <span class=\"kwrd\">from<\/span> emp m\r\n                                  <span class=\"kwrd\">where<\/span> 1=1\r\n                                    <span class=\"kwrd\">and<\/span> m.empno = e.mgr)\r\n <span class=\"kwrd\">where<\/span> 1=1\r\n   <span class=\"kwrd\">and<\/span> e.mgr <span class=\"kwrd\">is<\/span> <span class=\"kwrd\">not<\/span> <span class=\"kwrd\">null<\/span>\r\n\/<\/pre>\n<\/div>\n<p>This is of course a pretty simple example, but you can imagine what would happen if you want to update more columns, create a complex sub query or worse, make modifications to the predicates. You are more than likely going to forget one or more sub queries giving you an undesired result.<\/p>\n<p>update December 25th 2015: Find a demonstration script on <a href=\" https:\/\/livesql.oracle.com\/apex\/livesql\/s\/cmwvybogp9rz26hndjixdmprh\" target=\"_new\">LiveSQL<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is something I knew somewhere in the back of my head, but had forgotten about until now. When you want to update multiple columns in a single SQL statement based on a sub query you can of course duplicate this query for every column you want to update. But this violates the SPOD (Single [&hellip;]<\/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-647","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\/647","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=647"}],"version-history":[{"count":7,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/647\/revisions"}],"predecessor-version":[{"id":654,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/647\/revisions\/654"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=647"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=647"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=647"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}