CODEWITHSUNDEEP
sqlplsqloracle11gsql-merge
Kaveri Mandlik
Kaveri Mandlik

Reputation: 44

What’s wrong in this merge operation?

I am performing a MERGE operation in Oracle 11g, but they are returning more rows than the expected.

create table sales(product varchar2(20),month date, amount number(10))
insert into sales values('LG','01-Jan-17',20000);
insert into sales values('sony','01-Jan-18',22000);
insert into sales values('panasonic', '22-dec-17',18000);

create table sales_history(product varchar2(20),month date, amount number(10))
insert into sales_history values('sony', '22-dec-17',24000);
insert into sales_history values('panasonic', '22-dec-17',18000);

select * from sales;
select * from sales_history

merge into sales_history sh using(select product,month,amount from sales)s
on (s.product=sh.product)
when matched then update set sh.month=s.month,sh.amount=s.amount
when not matched then insert(sh.product,sh.month,sh.amount)
values(s.product,s.month,s.amount);

And I tried to do the same query in Pl/SQL which will give me the same results but it is returning more rows that is duplicated rows.Why is it so?

set serveroutput on
declare
s_product varchar2(20);
s_month date;
s_amount number(10);
p_product s_product%type;
m_month s_month%type;
a_amount s_amount%type;

cursor sc1 is
select product,month,amount from sales;
cursor shc2 is
select product,month,amount from sales_history;

begin
open sc1;
open shc2;
loop <<l1>>
fetch sc1 into s_product,s_month,s_amount;
fetch shc2 into p_product,m_month,a_amount;
if s_product = p_product then
  if s_month <> m_month then
  update sales_history set month = s_month where product = s_product;
  end if;
  if s_amount <> a_amount then
  update sales_history set amount = s_amount where product = s_product;
  end if;
else
  INSERT INTO  sales_history(product, month, amount)
  SELECT product, month, amount FROM sales;
  dbms_output.put_line('DATA IS UPDATED');
end if;
exit when sc1%notfound;
exit when shc2%notfound;
end loop l1;
close sc1;
close shc2;
end;

select * from sales_history

Upvotes: 0

Views: 115

Answers (2)

Naveen Kumar
Naveen Kumar

Reputation: 2006

I have tried executing the mentioned case in Oracle 12c and it is working. Please see the output mentioned below:

create table sales(product varchar2(20),month date, amount number(10))

Affected rows: 0
Time: 0.012s

insert into sales values('LG','01-Jan-17',20000)

Affected rows: 1
Time: 0.003s

insert into sales values('sony','01-Jan-18',22000)

Affected rows: 1
Time: 0.004s

insert into sales values('panasonic', '22-dec-17',18000)

Affected rows: 1
Time: 0.003s

create table sales_history(product varchar2(20),month date, amount number(10))

Affected rows: 0
Time: 0.004s

insert into sales_history values('sony', '22-dec-17',24000)

Affected rows: 1
Time: 0.002s

insert into sales_history values('panasonic', '22-dec-17',18000)

Affected rows: 1
Time: 0.003s

merge into sales_history sh using(select product,month,amount from sales)s
on (s.product=sh.product)
when matched then update set sh.month=s.month,sh.amount=s.amount
when not matched then insert(sh.product,sh.month,sh.amount)
values(s.product,s.month,s.amount)

Affected rows: 3
Time: 0.015s

Upvotes: 2

Mansi Raval
Mansi Raval

Reputation: 371

When you write

if s_product = p_product then
<<your code>>
else
<<your code>>

And when the first product name is sony from table sales, it does match with sony in sales_history and give the desired output, but also it does not match with the other entry in sales_hist and execute the else part of your code which is insert. This gives you multiple row as result.

See the below output. I tried with multiple dbms output. The else part is getting executed when sony is different from panasonic and so on.

set serveroutput on
declare
s_product varchar2(20);
s_month date;
s_amount number(10);
p_product s_product%type;
m_month s_month%type;
a_amount s_amount%type;

cursor sc1 is
select product,month,amount from sales;
cursor shc2 is
select product,month,amount from sales_history;

begin
open sc1;
open shc2;
loop <<ll>>
fetch sc1 into s_product,s_month,s_amount;
fetch shc2 into p_product,m_month,a_amount;
dbms_output.put_line('outside if');
dbms_output.put_line(s_product);
dbms_output.put_line(p_product);
if s_product = p_product then
  dbms_output.put_line(p_product);
  if s_month <> m_month then
  dbms_output.put_line(m_month);
  update sales_history set month = s_month where product = s_product;
  end if;
  if s_amount <> a_amount then
  update sales_history set amount = s_amount where product = s_product;
  end if;
else
  INSERT INTO  sales_history(product, month, amount)
  SELECT product, month, amount FROM sales;
  dbms_output.put_line('DATA IS UPDATED');
end if;
exit when sc1%notfound;
exit when shc2%notfound;
end loop l1;
close sc1;
close shc2;
end;

Output

outside if
LG
sony
DATA IS UPDATED
outside if
sony
panasonic
DATA IS UPDATED
outside if
panasonic
panasonic
panasonic

Upvotes: 1

Related Questions