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
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