Saturday, November 12, 2011

Sequences (Part 2)


In this example, sequence numbers are added to a table retrospectively. First create a table and add some data to it:

SQL> create table my_table
  2  (first_name varchar2(10))
  3  /
 
Table created.
 
SQL> insert into my_table values ('Andrew')
  2  /
 
1 row created.
 
SQL> insert into my_table values ('Brian')
  2  /
 
1 row created.
 
SQL> insert into my_table values ('Colin')
  2  /
 
1 row created.
 
SQL> insert into my_table values ('Desmond')
  2  /
 
1 row created.

SQL>
  
Then add an extra column to hold the sequence number:
  
SQL> alter table my_table add
  2  (sequence_no number)
  3  /
 
Table altered.

SQL>
  
Create the sequence:
  
SQL> create sequence my_sequence start with 1
  2  /
 
Sequence created.

SQL>
  
And finally, add the sequence numbers with a simple update statement:
  
SQL> update my_table set sequence_no =
  2  my_sequence.nextval
  3  /
 
4 rows updated.
 
SQL> select * from my_table
  2  /
 
FIRST_NAME SEQUENCE_NO
---------- -----------
Andrew               1
Brian                2
Colin                3
Desmond              4
 
SQL>

No comments: