Adding information

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 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.

In code, we have:

CREATE TABLE "VULDOEL" 

   (    "I" NUMBER, 

    "NAAM" CHAR(10)

   ) ;

REM INSERTING into VULDOEL

SET DEFINE OFF;

Insert into VULDOEL (I,NAAM) values ('1','tom       ');

Insert into VULDOEL (I,NAAM) values ('2','ine       ');

Insert into VULDOEL (I,NAAM) values ('3',null);

Insert into VULDOEL (I,NAAM) values ('4',null);

Insert into VULDOEL (I,NAAM) values ('5',null);

CREATE TABLE "BIJDRAGE" 

   (    "I" NUMBER, 

    "NAAM" CHAR(10)

   )

REM INSERTING into BIJDRAGE

SET DEFINE OFF;

Insert into BIJDRAGE (I,NAAM) values ('3','Paula     ');

Insert into BIJDRAGE (I,NAAM) values ('4','Stella    ');

Insert into BIJDRAGE (I,NAAM) values ('5','Bart      ');

The solution to load the missing information is:

update vuldoel A 

set A.naam = (select B.naam from bijdrage B where B.I = A.I)

where exists (select B.I from bijdrage B where A.I=B.I);

commit;