How to update multiple values in oracle?

Question

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!

Why "a for loop"? This can all be done in a single SQL statement; there are no "for loops" in SQL. — user5683823
– user5683823, Commented Sep 27, 2021 at 1:44
Until you learn how to format code, please don't edit again. (It's easy: select the code, and then use the {} formatting button on the formatting menu.) I edited to remove all references to "loops" - there are no "loops" of any kind ("for loop" or any other) in SQL. Also, "outcome" was correct; "output" is what you get from a SELECT query, not from updating a table. — user5683823
– user5683823, Commented Sep 27, 2021 at 2:00

user5683823 · Accepted Answer · 2021-09-27 02:01:36Z

1

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
;

answered Sep 27, 2021 at 2:01

user5683823

Sign up to request clarification or add additional context in comments.

1 Comment

Qiteng Feng Over a year ago

Thank you sir! It's first time for me to ask question in this website. I am trying to get familiar of it. Thanks again for instructing me on both coding and formatting.

Qiteng Feng · Accepted Answer · 2021-10-09 02:30:24Z

0

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;

edited Oct 9, 2021 at 2:30

answered Sep 27, 2021 at 8:59

Qiteng Feng

32 bronze badges

Collectives™ on Stack Overflow

How to update multiple values in oracle?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related