Home » SQL & PL/SQL » SQL & PL/SQL » Reg Updating a column with the sequence number (merged)
Reg Updating a column with the sequence number (merged) [message #332978] Thu, 10 July 2008 04:13 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
I have a non primary key column where i need to update sequence number in it ,now the column is empty , i have tried writing a procedure for it can any pls check over its correct or there is any possible way to update,below is the procedure

create or replace procedure awb_seq
is
    v_seq number(9);
    cursor C is select key,awb_number from ext_awbdata1 order by awb_number;
    
BEGIN
        
    For I in C Loop
    
    IF (I.key is null) then
    
    select ext_awbdata1_seq.nextval into v_seq from dual; 
    
    
    
    update ext_awbdata1
    set key = v_seq
    where awb_number = I.awb_number;
    
    end if;
    
    end loop;
    
END;


Pls comment on this

Thanks,
Re: Reg Updating a column with the sequence number [message #332986 is a reply to message #332978] Thu, 10 July 2008 04:30 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Possibly the least efficient way of doing that update without deliberately writing bad code.

1) Don't step through all the records in a cursor when you can do it as a single piece of sql.
2) If you are going to step through the records in a cursor, include all the tests that you can (the 'I.Key Is Null' in this case) in the cursor to reduce the number of rows you need to process.
3) Don't select the Sequence Nextval seperately - you can use it directly in the UPDATE.

I'd do it like this:
drop table test_0045;

create table test_0045 (col_1 number, col_2 number);

create sequence test_0045_seq;

insert into test_0045 values (10,99);
insert into test_0045 values (20,null);
insert into test_0045 values (30,null);
insert into test_0045 values (40,98);
insert into test_0045 values (50,97);
insert into test_0045 values (60,null);
insert into test_0045 values (70,96);

update test_0045
set col_2 = test_0045_seq.nextval
where col_2 is null;

select * from test_0045 order by col_1;

COL_1  COL_2     
-----  -----     
10     99        
20     1         
30     2         
40     98        
50     97        
60     3         
70     96        

7 rows selected


[Replace QUOTE tags with Code tags]

[Updated on: Thu, 10 July 2008 04:35]

Report message to a moderator

Previous Topic: Sequencial Incremental Rows result
Next Topic: Selecting from multiple queries in one statement
Goto Forum:
  


Current Time: Sat Dec 10 14:48:12 CST 2016

Total time taken to generate the page: 0.11848 seconds