How do I write a program that duplicates a row when multiple values in one column are assigned to a single value in another?

Question

I have three tables:

table1:

MODULE	EMPLOYEE
A	Billy Bob
A	Billy Joe
B	John Doe
B	Jane Doe
C	Catey Rice

table2: Primary_Key = (MATERIAL_ID, MATERIAL_NUM)

MATERIAL_ID	MATERIAL_NUM	MODULE
11111111111	222222222222	A
11111111112	222222222223	B
11111111113	222222222224	C

and I need a query that will fill in my third table so that it looks like this:

table3: Foreign_Key = (MATERIAL_ID, MATERIAL_NUM)

MATERIAL_ID	MATERIAL_NUM	EMPLOYEE
11111111111	222222222222	Billy Bob
11111111111	222222222222	Billy Joe
11111111112	222222222223	John Doe
11111111112	222222222223	Jane Doe
11111111113	222222222224	Catey Rice

I tried this query:

INSERT INTO table3(EMPLOYEE)
SELECT t1.EMPLOYEE
  FROM table1 t1
  FULL OUTER JOIN table2 t2
             ON t1.MODULE = t2.MODULE;

I already have MATERIAL_ID and MATERIAL_NUM filled in on table3 by inserting the data from table2. If I need to do it over with EMPLOYEE in the query I'll just empty the table and rewrite it.

The issue I am running into is this error: ORA-01400: cannot insert NULL into ("MATERIAL_ID"), which I figure is happening because I don't have a method that duplicates the rows as multiple names fill out the same MATERIAL_ID and MATERIAL_NUM. So I need help to create the method to do that.

It sounds like you're making more work for yourself than you need. Table 3 doesn't seem to need to exist at all - it's just the result of joining tables 1 and 2, but with the added opportunity of being inconsistent with them. — Damien_The_Unbeliever
Are values in table2.MODULE unique? If not, then full joining by it may cause troubles — Alexey Larionov
@Damien_The_Unbeliever I created table3 so that there is direct correlation between the material and the employee who is responsible for it. It will also be used for a material claiming tool where employees can directly select the material by it's id/number. — TheSchmidly
@AlexeyLarionov modules are not unique, but I'm not sure how else to join the tables — TheSchmidly

Gordon Linoff Gordon Linoff · Accepted Answer · 2021-06-15T12:14:36

I think you want to UPDATE the employee column, not INSERT new rows:

update table3 t3
    set employee = (select t1.employee
                    from table1 t1 join
                         table2 t2
                         on t2.module = t1.module
                    where t3.materialid = t2.materialid
                   );

How do I write a program that duplicates a row when multiple values in one column are assigned to a single value in another?

1 Answers