Sequences (Part 2)

Go to part 1 

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>