Home » SQL & PL/SQL » SQL & PL/SQL » Cursor question
Cursor question [message #246737] Thu, 21 June 2007 13:17 Go to next message
ajgrobertson
Messages: 3
Registered: June 2007
Junior Member
Hello,

I am trying to use a cursor to update a field in a table with a number sequence (starting at 1) depending on date the record was entered in the system (i.e. First record is 1, second is 2 etc.). This sequence also needs to be reset for each ID in the table, so an example of how the sequence should look is as follows:

ID Date Seq
A 01/01/05 1
B 05/06/05 1
B 12/12/06 2
B 05/06/07 3
C 01/01/05 1

So far I have a cursor that can loop through and update the records, but cannot figure out how to increase the seq or how to reset the cursor for each ID. Anyone have any ideas on how I might achieve this?

Thanks,
AR
Re: Cursor question [message #246740 is a reply to message #246737] Thu, 21 June 2007 13:25 Go to previous messageGo to next message
ajgrobertson
Messages: 3
Registered: June 2007
Junior Member
Here is the code I have so far:

    cursor Test_cursor is
    select id, date, seq
    from test
    order by id, date, seq
    for update of seq;
    
    l_id number(10);
    l_seq number;
    
    test_record test_cursor%ROWTYPE;
    
    test_counter number(15);   
    
    begin
        open test_cursor;
        test_counter :=0;
    
            loop
                l_id :=0;
        
                fetch test_cursor into test_record;
                exit when test_cursor%NOTFOUND;
                test_counter := test + 1;
                l_id := test_record.id;
                
                update test
                set seq = 1
                where current of test_cursor;
                                                    
            end loop;
        close test_cursor;
     end;
Re: Cursor question [message #246746 is a reply to message #246737] Thu, 21 June 2007 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 SQL ways:
SQL> select * from test order by id, mydate;
I MYDATE          SEQ
- -------- ----------
A 01/01/05
B 05/06/05
B 12/12/06
B 05/06/07
C 01/01/05

5 rows selected.

SQL> update test a
  2  set seq = ( select rn 
  3              from ( select rowid row_id,
  4                            row_number() over (partition by id order by mydate) rn
  5                     from test
  6                   ) b 
  7            where b.row_id = a.rowid )
  8  /

5 rows updated.

SQL> select * from test order by id, mydate;
I MYDATE          SEQ
- -------- ----------
A 01/01/05          1
B 05/06/05          1
B 12/12/06          2
B 05/06/07          3
C 01/01/05          1

5 rows selected.

SQL> rollback;

Rollback complete.

SQL> merge into test a
  2  using (select id, mydate, 
  3                row_number() over (partition by id order by mydate) rn
  4         from test) b
  5  on (a.id = b.id and a.mydate = b.mydate)
  6  when matched then update set seq = b.rn
  7  /

5 rows merged.

SQL> select * from test order by id, mydate;
I MYDATE          SEQ
- -------- ----------
A 01/01/05          1
B 05/06/05          1
B 12/12/06          2
B 05/06/07          3
C 01/01/05          1

5 rows selected.

Regards
Michel
Re: Cursor question [message #246751 is a reply to message #246737] Thu, 21 June 2007 14:20 Go to previous message
ajgrobertson
Messages: 3
Registered: June 2007
Junior Member
Michel,

Thank you so much, that works great.

AR
Previous Topic: Delete file from UNIX box through PL/SQL block.
Next Topic: Index on aTable
Goto Forum:
  


Current Time: Sat Dec 10 20:32:01 CST 2016

Total time taken to generate the page: 0.07680 seconds