How to update multiple values in oracle?-CodePudding

I have two tables:

table 1:
|Project type|Quarter1|Quarter2|Quarter3|Quarter4|
|------------|--------|--------|--------|--------|
|type1       |1       |3       |5       |7       |
|type2       |2       |4       |6       |8       |

table 2:
|Project|Value|Quarter|
|-------|-----|-------|
|type1  |     |1      |
|type2  |     |1      |
|type1  |     |2      |
|type2  |     |2      |
|type1  |     |3      |
|type2  |     |3      |
|type1  |     |4      |
|type2  |     |4      |

I want to update table 2 value section with data from table 1 and the expected outcome is:

|Project|Value|Quarter|
|-------|-----|-------|
|type1  |1    |1      |
|type2  |2    |1      |
|type1  |3    |2      |
|type2  |4    |2      |
|type1  |5    |3      |
|type2  |6    |3      |
|type1  |7    |4      |
|type2  |8    |4      |

I know updating single one value can be written as:

update table2 a 
   set a.value = (select Quarter1 
                    from table1
                   where projecttype = 'type1')
 where a.project = 'type1'
   and a.quarter = '1';

Please tell me how can I update all value in one time?

Thank you!

CodePudding user response：

One way is with a merge statement:

merge into table_2 t
  using    table_1 s
     on    (t.project = s.project_type)
when matched then update
  set t.value = case t.quarter when 1 then s.quarter1
                               when 2 then s.quarter2
                               when 3 then s.quarter3
                               when 4 then s.quarter4 end
;

CodePudding user response：

This is my primary thought about using loop to repeat the updating process. The main body refer to mathguy's answer (Thanks again). It may complicate the code in this scenario, but would be useful when there are numerous columns in table1, such as years instead quarters.

declare
  
  quart_num number;
  code      varchar2(2000);
begin

  for quart_num in 1..4
  loop
      code := 'merge into table2 a
               using table1 b
               on (a.project = b.projecttype)
               when matched then
               update set a.value = quarter'||
               quart_num || 'where a.quarter =' ||quart_num;
      execute immediate(code);
   end loop:
end;