Home » SQL & PL/SQL » SQL & PL/SQL » Need help for updating table records
Need help for updating table records [message #188104] Thu, 17 August 2006 02:30 Go to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Hi,

I have a requirement wherein i need to update the table having some .5 million records in groups of 5. Say for example 1st row should have same data (few columns) as 6th row, 2nd row should have same data as 7th row and like wise. The group of records that are to be replicated is 5. To achieve this i wrote a plsql block for updating the rows as mentioned below. This block replicates the records (1,6,11....) when the v_start =5 in the body and v_start =1 in declare. the problem with this is its taking too much of time. Please suggest.

Declare
v_start number:=5;
v_count number:=0;
v_commit number:=0;
begin
select count(*) into v_count from xyz_prashant;
FOR i in 1..v_count loop
if (v_start=i) then
UPDATE xyz_prashant SET a='EE',b =55
WHERE ROWID = (SELECT id FROM
(select id,r from (select rowid id,rownum r,a from xyz_prashant) where r= v_start));
v_commit:=v_commit+1;
if (mod(v_commit,1000)=0) then
commit;
end if;
v_start:=v_start+5;
end if;
end loop;
commit;
end;

Thanks
Re: Need help for updating table records [message #188105 is a reply to message #188104] Thu, 17 August 2006 02:41 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Tip 1: don't commit in the loop. Just don't.
Tip 2: what do you consider 'first'? Oracle is a relational database. One feature of a relational database is that it has no order whatsoever.
Tip 3: Your task can probably be achieved in a single SQL statement, but in order to get more detailed help we need a sample data script (create table, insert, ...).

MHE

Re: Need help for updating table records [message #188108 is a reply to message #188105] Thu, 17 August 2006 02:51 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Dear MHE,

thanks for your time, following is the sample script.


CREATE TABLE XYZ_PRASHANT
(
A VARCHAR2(10 BYTE),
B NUMBER
)


Insert into xyz_prashant
(A, B)
Values
('AA', 11);
Insert into xyz_prashant
(A, B)
Values
('BB', 22);
Insert into xyz_prashant
(A, B)
Values
('CC', 33);
Insert into xyz_prashant
(A, B)
Values
('DD', 44);
Insert into xyz_prashant
(A, B)
Values
('EE', 55);
Insert into xyz_prashant
(A, B)
Values
('AA', 11);
Insert into xyz_prashant
(A, B)
Values
('BB', 22);
Insert into xyz_prashant
(A, B)
Values
('CC', 33);
Insert into xyz_prashant
(A, B)
Values
('DD', 44);
Insert into xyz_prashant
(A, B)
Values
('EE', 55);
Insert into xyz_prashant
(A, B)
Values
('AA', 11);
Insert into xyz_prashant
(A, B)
Values
('BB', 22);
Insert into xyz_prashant
(A, B)
Values
('CC', 33);
Insert into xyz_prashant
(A, B)
Values
('DD', 44);
Insert into xyz_prashant
(A, B)
Values
('EE', 55);
Insert into xyz_prashant
(A, B)
Values
('AA', 11);
Insert into xyz_prashant
(A, B)
Values
('BB', 22);
Insert into xyz_prashant
(A, B)
Values
('CC', 33);
Insert into xyz_prashant
(A, B)
Values
('DD', 44);
Insert into xyz_prashant
(A, B)
Values
('EE', 55);

I need to update the columns A & B with same values in the rows 1,6,11 with update statement as
update xyz_prashant set a='A',b=1
............similarly columns A & B with same values in the rows 2,7,12.......with update xyz_prashant set a='B',b=2
same will happen for other 3 update statements so that the set of 5 updates will be replicated across the table.

Thanks
Re: Need help for updating table records [message #188112 is a reply to message #188108] Thu, 17 August 2006 03:08 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:

UPDATE XYZ_PRASHANT
SET A = SUBSTR(A,1,1),
B = SUBSTR(B,1,1)


Thumbs Up
Rajuvan
Re: Need help for updating table records [message #188121 is a reply to message #188112] Thu, 17 August 2006 03:31 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Raju,

Thanks for the tip but the values present in the table are just the sample values. Actual table has data which can't be handled by string handling.

Re: Need help for updating table records [message #188122 is a reply to message #188121] Thu, 17 August 2006 03:34 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
be2sp1 wrote on Thu, 17 August 2006 10:31

Raju,

Thanks for the tip but the values present in the table are just the sample values. Actual table has data which can't be handled by string handling.


Can't you provide a more reliable sample case then? Thanks for the prompt reply, by the way.

MHE
Previous Topic: recycle bin
Next Topic: RAD tools for PL/SQL web development application
Goto Forum:
  


Current Time: Wed Dec 07 14:36:45 CST 2016

Total time taken to generate the page: 0.05534 seconds