MERGE INTO using Oracle 8i PL/SQL

[jestrada101]

Hello,
I have a merge into script that runs on Oracle 9i and I need to make it somehow run on Oracle 8i or create something similiar using PL/SQL.

It selects information from one table and inserts or updates information in another table.

Any guidance greatly appreciated..

----my merge---------------------
merge into mytable2 my2
using (
select
mydate,
sum(amount) amount
from
mytable
group by
mydate
) CH
on
(
my2.mydate = CH.mydate
)
when matched then
update set
my2.amount=(my2.amount + ch.amount)
when not matched then insert
(
mydate,amount
) values ( ch.mydate, ch.amount);

 

[mpcurry]

FOR r1 into (
select
mydate,
sum(amount) amount
from
mytable
group by
mydate)
loop
begin
update my2
set my2.amount=(my2.amount + r1.amount)
where my2.mydate = r1.mydate;
IF ( SQL%NOTFOUND )
then
insert into my2
(mydate,amount)
values
( r1.mydate, r1.amount);
end if;
end;
end loop;

 

[jestrada101]

I cant seem to get this to work, should it begin with "BEGIN"?

Should "r1" be declared?

Thanks
JE

 

[sem]

Obviously, as well as any pl/sql block, it should be enclosed in begin..end;
You don't need to declare r1, as it's declared implicitly and is visible within that loop only.

Regards, Dima

http://seminihin.narod.ru

 

[jestrada101]

Thanks.. I got this work.. however, my update is very slow..

The detailed table will have over 1 millions records..

any ideas on how to speed up this update?

Thanks!
JE