Skip to Main Content
  • Questions
  • Insert/update table based on condition

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Arshad.

Asked: December 07, 2016 - 9:23 am UTC

Last updated: February 26, 2019 - 2:56 am UTC

Version: 12c Enterprise Edition(livesql)

Viewed 10K+ times! This question is

You Asked

We have 2 tables

one having 4 columns and second has 2 columns.

Create table A(Currency_old varchar(20),Currency_indicator_old varchar(20),Currency_New varchar(20),Currency_indicator_new varchar(20));
Create table B (Currency_old varchar(20),Currency_indicator_old varchar(20));

We have requirement that we need to compare data from B with first two columns of table A and update/insert in the table B with values in 3rd and 4th column of table A.

by update/insert i mean we should either update the two columns where match is found and replace them with entries from table A(3rd and 4th column) or else Insert an additional row in table B with 3rd and 4th column from A without replacing the row.

Example:

Table A

Currency_old Currency_indicator_old Currency_New Currency_indicator_new
GBR GBRO LGBR LGBRO


Table B
Currency_old Currency_indicator_old
GBR GBRO

Since Table A and Table B has matching 1st and 2nd column we need to insert and entry in B

Table B
Currency_old Currency_indicator_old
GBR GBRO
LGBR LGBRO

In case if we are updating result should be

Table B
Currency_old Currency_indicator_old
LGBR LGBRO

In case no match, do nothing.

In case Match not found we do not need to do anything.

I have tried using Merge clause for the same also not able to find solution using update clause but not able to achieve the result , can you let me know how can this be achieved.

and Chris said...

If you only want to insert the new values into b when there exists a match on old a values, you can:

- Join A and B in the using clause of the merge
- Merge on the results of this
- Insert the new values when there's no match

For example:

Create table A(
  Currency_old varchar(20),Currency_indicator_old varchar(20),
  Currency_New varchar(20),Currency_indicator_new varchar(20)
);
Create table B (
  Currency_old varchar(20),Currency_indicator_old varchar(20)
);

insert into a values ('GBR','GBRO','LGBR','LGBRO');
insert into b values ('GBR','GBRO');
commit;

merge into b 
using (select a.* from a join b 
       on a.Currency_old = b.Currency_old 
       and a.Currency_indicator_old = b.Currency_indicator_old) a
on    ( a.Currency_new = b.Currency_old 
and   a.Currency_indicator_new = b.Currency_indicator_old )
when not matched then insert
 values (a.Currency_new, a.Currency_indicator_new); 
 
select * from b;

CURRENCY_OLD  CURRENCY_INDICATOR_OLD  
GBR           GBRO                    
LGBR          LGBRO  

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank You

Arshad, December 08, 2016 - 12:58 pm UTC

Exactly what i was looking for, Can you please let me know how can we update the old values in B with new values in A if Match found.
Thanks in advance for the help.
Chris Saxon
December 08, 2016 - 1:24 pm UTC

I don't understand what you're trying to do. The example I gave is when there is a match in A. So why do you need to update? You'll end up with two rows in B with the same values!

Replace

Arshad, December 08, 2016 - 1:42 pm UTC

I have another requirement where we may need to replace the old values with new ones,so i wish to replace the old value in B with the new values in A,i believe we will need to update in that case.

Chris Saxon
December 08, 2016 - 2:10 pm UTC

But how do you know whether you're replacing the existing values or inserting alongside them?

Either insert or update

Arshad, December 08, 2016 - 2:20 pm UTC

We are not trying both at a time either we will be inserting or else replacing the older.
Chris Saxon
December 08, 2016 - 4:35 pm UTC

I still don't understand how you decide whether you update or insert.

Anonymous, February 25, 2019 - 6:03 pm UTC

Nice Tom... But I have a requirement. It is as follows
I need to create a procedure which takes an ID say it is a varchar
I have a select query which has union and returns me a single record based on the ID

One column from the result of select query decides in which table the data to be inserted assume in one of the two tables

How to write a procedure. Can u help me?

Connor McDonald
February 26, 2019 - 2:56 am UTC

Pseudo-code below

create or replace
procedure my_proc(id varchar2) is
  result int;
begin
  select ...
  into   result
  from 
   ( select ...
     union 
     select ...
   );

  if result = 1 then
     insert into TABLE_A ...
  elsif result = 2 then
     isnert into TABLE_B ...
  end if;
end;