Home » SQL & PL/SQL » SQL & PL/SQL » updating a huge database
updating a huge database [message #35980] Sun, 28 October 2001 17:46 Go to next message
yilmaz
Messages: 4
Registered: September 2001
Junior Member
hi guys,
i have a big problem in updating an oracle 8i 1.7 database table with more than 50000 rows and 20 columns. The problem is i have another table with approximately 17000 rows and 12 columns. The latter one holds newer data, and most of them have the same primery key as the previous one (16000). Now, i want to update the older table with newer data in the new table. I wrote a pl/sql procedure to achieve this task, but first, it takes too long ( more than 3 hours) , secondly the update task is not achieved, i mena there were no updated data.
can you have a look at the below procedure and tell me what is wrong with it and where i am making mistake?
thank alot for your help in advance.
cheers :)
my Pl/sql procedure :
********************
declare
cursor c1 is

select totid,unit from test where unit is not null;
--totid is the primary key of the table
--test is the newer table
tot csource.TOTID%type;--tot var will hold the totid values
uni csource.unit%type;--uni var will hold the column data that will be updated
begin
open c1;
loop
fetch c1 into tot,uni;
exit when c1%notfound;
update csource set unit=uni where trim(totid)=tot;
--csource is the older table
end loop;
commit;
close c1;
end;

----------------------------------------------------------------------
Re: updating a huge database [message #35992 is a reply to message #35980] Mon, 29 October 2001 07:21 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You should do this in just a straight update statement - no need for PL/SQL here:

update csource cs
set unit =
(select unit from test t
where t.totid = trim(cs.totid)
and t.unit is not null)
where exists
(select 1 from test t
where t.totid = trim(cs.totid)
and unit is not null);

You will definitely want an index on totid in test (if this column is the primary key, than the index is already in place).

----------------------------------------------------------------------
Previous Topic: using values of dynamic sql
Next Topic: storing a negative numerical value in a field of data type numeric
Goto Forum:
  


Current Time: Fri May 27 15:01:02 CDT 2022