{"id":2422,"date":"2019-01-08T22:18:22","date_gmt":"2019-01-08T21:18:22","guid":{"rendered":"http:\/\/van-maanen.com\/?p=2422"},"modified":"2019-01-08T22:18:22","modified_gmt":"2019-01-08T21:18:22","slug":"adding-information","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=2422","title":{"rendered":"Adding information"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Today, I struggled a bit with a simple situation. I had a table with a primary key and an additional field that was partially filled. I also had a secondary table with a key and data were to be loaded in the first table.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In a scheme, the situation looked like:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/01\/Drawing1-1-1.jpg\" alt=\"\" class=\"wp-image-2423\"\/><figcaption>loading additional data<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In this example we have a table A with a primary key having values 1,2,3,4,5 with an additional field that is partially field. With key=3,4,5, we have no value. This value is to be loaded from another table that has key 3,4,5 and a field with values.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In code, we have:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE TABLE \"VULDOEL\" <br>\n   (    \"I\" NUMBER, <br>\n    \"NAAM\" CHAR(10)<br>\n   ) ;<br>\nREM INSERTING into VULDOEL<br>\nSET DEFINE OFF;<br>\nInsert into VULDOEL (I,NAAM) values ('1','tom       ');<br>\nInsert into VULDOEL (I,NAAM) values ('2','ine       ');<br>\nInsert into VULDOEL (I,NAAM) values ('3',null);<br>\nInsert into VULDOEL (I,NAAM) values ('4',null);<br>\nInsert into VULDOEL (I,NAAM) values ('5',null);<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE TABLE \"BIJDRAGE\" <br>\n   (    \"I\" NUMBER, <br>\n    \"NAAM\" CHAR(10)<br>\n   )<br>\nREM INSERTING into BIJDRAGE<br>\nSET DEFINE OFF;<br>\nInsert into BIJDRAGE (I,NAAM) values ('3','Paula     ');<br>\nInsert into BIJDRAGE (I,NAAM) values ('4','Stella    ');<br>\nInsert into BIJDRAGE (I,NAAM) values ('5','Bart      ');<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The solution to load the missing information is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">update vuldoel A <br>\nset A.naam = (select B.naam from bijdrage B where B.I = A.I)<br>\nwhere exists (select B.I from bijdrage B where A.I=B.I);<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">commit;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today, I struggled a bit with a simple situation. I had a table with a primary key and an additional field that was partially filled. I also had a secondary table with a key and data were to be loaded in the first table. In a scheme, the situation looked like: In this example we [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-2422","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/2422","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2422"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/2422\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2422"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2422"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2422"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}