Updating table while joining across multiple tables

Question

I am (fairly new to) using PL/SQL with Oracle 12c, and am having trouble with a procedure I am trying to implement. I've looked up a lot of tutorials and similar questions here, but so far I've had no luck finding anything that could help me. The situation is this:

I have three tables: table1, table2, and table3.

Table1 has the attributes detailA and detailB.

Table2 has the attributes detailB and detailC.

Table3 has the attributes detailC and detailD.

My goal is to update the value of detailA in table1 to be equal to detailD in table2. I'm aware that in order to do that I need to join table1 and table2 on detailB, then join table2 and table3 on detailC. Actually implementing that, though, is giving me trouble.

So far, everything I've tried goes off-rails about halfway through—I'm having a very hard time implementing all the conditions I need to check while still keeping it within the structure of PL/SQL.

Here's an (obviously very wrong) example of something I've tried:

    UPDATE (SELECT table1.detailB, table2.detailB, table2.detailC, 
    table3.detailC, table3.detail4 FROM table1
    JOIN table2 on table1.detailB =  table2.detailB
    JOIN table3 on table2.detailC = table3.detailD)
    SETtable1.detailA = table3.detail4;

If anyone could help me understand this better, I'd be very grateful.

EDIT: And for an example with some actual data, if I inserted the following, it should replace the 100 in table1 with the 1000 in table4 when I run my query:

INSERT INTO table1(detailA, detailB) VALUES (100, 200);
INSERT INTO table2(detailB, detailC) VALUES (200, 400);
INSERT INTO table3(detailC, detailD) VALUES(400, 1000);

Answer 1

If I understand your problem correctly (sample data would be much of help), then I would use MERGE statement as follows (Tested):

MERGE INTO table1 -- The table you want to update 
USING 
(
  SELECT t2.detailB detailb, t2.detailC, t3.detailD detailD
  FROM table2 t2 inner join table3 t3
  ON (t2.detailC = t3.detailC)
) ta ON (ta.detailB = table1.detailB)
WHEN MATCHED THEN UPDATE 
    SET table1.detailA = ta.detailD;

Tested in FIDDLE

Answer 2

when you updated column to listed in select query like this

UPDATE (SELECT table1.detailB, 
                table2.detailB, 
                table2.detailC, 
                table3.detailC, 
                table3.detail4,
                table1.detailA 
         FROM table1
    JOIN table2 on table1.detailB =  table2.detailB
    JOIN table3 on table2.detailC = table3.detailD)
    SET detailA = detail4;

Answer 3

Try this:

UPDATE (SELECT  t1.detailA,
                t1.detailB, 
                t2.detailB, 
                t2.detailC, 
                t3.detailC, 
                t3.detailD                    
        FROM table1 t1 JOIN table2 t2 ON t1.detailB =  t2.detailB
                       JOIN table3 t3 ON t2.detailC =  t3.detailC)

SET t1.detailA = t3.detailD;

Answer 4

The first question you should answer is what to do if the update is not deterministic. Consider the following data

INSERT INTO table1(detailA, detailB) VALUES (100, 200);
INSERT INTO table2(detailB, detailC) VALUES (200, 400);
INSERT INTO table3(detailC, detailD) VALUES(400, 1000); -- which value should I update?? 1000
INSERT INTO table3(detailC, detailD) VALUES(400, 1001); -- or 1001

The typical answer ist to consider this as invalid data and to set up constraints that disables them. Note that Oracle protect this with error ORA-01779: cannot modify a column which maps to a non key-preserved table

You may define primary keys as follows to make the update safe:

alter table table2 add (primary key (detailB));
alter table table3 add (primary key (detailC));

After that you can perform update of a join (inline) view

UPDATE (
SELECT table1.detailA, table3.detailD 
FROM table1
    JOIN table2 on table1.detailB =  table2.detailB
    JOIN table3 on table2.detailC = table3.detailC
)
set detailA = detailD
;

.

SELECT table1.detailA 
FROM table1
;

1000