How to update a table joined with another table multiple times in Oracle?

Question

I have three fields in my table and I want to update them by joining three times to another table. I know that I have to use merge into, but I couldn't find any similar query that uses merge into and join with only one table for multiple times.

The select statement is something like this:

select * from TABLE t
inner join DESTINATION_TABLE d1
on t.CODE1 = d1.CODE
inner join DESTINATION_TABLE d2
on t.CODE2 = d2.CODE
inner join DESTINATION_TABLE d3
on t.CODE3 = d3.CODE

Now how can I update three fields (FIELD1, FIELD2, FIELD3) from TABLE by using fields from d1, d2, and d3 using merge into?

EDIT:

The original query is:

select * from TOTAL
inner join GROUP_LEVEL_DETAIL gl1
on gl1.NAME = substr(GL, 1, instr(GL, ' -', 1))
inner join GROUP_LEVEL_DETAIL gl2
on GL2.NAME = replace(substr(GL, instr(GL, ' -', 1, 1), instr(GL, ' -', 1, 2) - instr(GL, ' -', 1, 1)), ' - ', '')
inner join GROUP_LEVEL_DETAIL gl3
on gl3.NAME = replace(substr(GL, instr(GL, '-', 1, 2), 500), '- ', '')

The sample data for TOTAL is:

  ID                GL                GL1_CODE   GL2_CODE   GL3_CODE
-----  ----------------------------- ---------- ---------- -----------
  1     Sample1 - Sample2 - Sample3    null        null       null
  2     John - Jack - Harry            null        null       null

The sample data for GROUP_LEVEL_DETAIL is:

  CODE         NAME        LEVEL_NO
---------  -----------   ------------
  SMP1       Sample1           1
  SMP2       Sample2           2
  SMP3       Sample3           3
  JCK1       Jack              1
  JHN2       John              2
  HRY3       Harry             3

And I want my TOTAL table get like this after the update:

  ID                GL                GL1_CODE   GL2_CODE   GL3_CODE
-----  ----------------------------- ---------- ---------- -----------
  1     Sample1 - Sample2 - Sample3    SMP1        SMP2       SMP3
  2     John - Jack - Harry            JCK1        JHN2       HRY3

I think you need to supply some **sample data** and the **expected result** or you could set-up a problem demo e.g. https://dbfiddle.uk/ — Paul Maxwell, Dec 11 '18 at 08:03
fields belong to which table and update by which columns in d1 d2 d3??? — Nikhil S, Dec 11 '18 at 08:18
@APC Now I updated the question. I didn't mean to waste anyone's time. I just wrote it that way, in order to prevent overcomplication. — iminiki, Dec 11 '18 at 08:37

APC · Accepted Answer · 2018-12-11T10:15:04.833

From your revised question, I think the solution is to assemble the GROUP_LEVEL_DETAIL names in the subquery to make a key which you can join to the TOTAL table.

merge into TOTAL t
using ( select d1.code as d1_code
               , d2.code as d2_code
               , d3.code as d3_code
               , d1.name || ' - ' ||
                 d2.name || ' - ' ||
                 d3.name as joined_code
         from  GROUP_LEVEL_DETAIL d1
         cross join GROUP_LEVEL_DETAIL d2
         cross join GROUP_LEVEL_DETAIL d3
         where d1.level_no = 1
         and   d2.level_no = 2
         and   d3.level_no = 3  
       ) d
on ( t.gl = d.joined_code )
when matched then
    update 
    set t.gl_code1 = d.d1_code
        ,  t.gl_code2 = d.d2_code
        ,  t.gl_code3 = d.d3_code

The USING subquery generates a result set of all possible permutations of records from GROUP_LEVEL_DETAIL. After your revision I have included a WHERE clause to enforce the implied rule GL = 'level 1 - level 2 - level 3'. You may wish to not do that (perhaps not every record in TOTAL has a valid GL) or extend the WHERE clause to apply any other rules regarding valid combinations.

That's what I was looking for. I believe you already had written the right answer before my edit. Thank you. — iminiki, Dec 11 '18 at 08:58

score 0 · Answer 2 · answered Dec 11 '18 at 08:13

When the joins are based on primary keys or unique index you should be able to do the following:

--create tables and unix index
create table table1 (code number, field1 number, field2 number, field3 number);
create unique index table1_code_inx on table1 (code);

create table table2 (code number, field1 number, field2 number, field3 number);
create unique index table2_code_inx on table2 (code);

-- update data from table2 to table1
update( select a.FIELD1 A_FIELD1, a.FIELD2 A_FILED2, a.FIELD3 A_FIELD3,
               b.FIELD1 B_FIELD1, b.FIELD2 B_FILED2, b.FIELD3 B_FIELD3 
          from TABLE1 a, TABLE2 b
          where a.CODE = b.CODE )
set A_FIELD1 = B_FIELD1,
    A_FILED2 = B_FILED2,
    A_FIELD3 = B_FIELD3;

How to update a table joined with another table multiple times in Oracle?

2 Answers2