Re: unique id for a populated table

I recommend that you use an Oracle Sequence instead of the ROWNUM suggested by Prasad Chavali.
In Oracle, a ROWNUM is a pseudo-column whose values are not unique per record and they will differ based on the number of rows in the table and the Order By clause. The values returned by an Oracle Sequence will always be unique, it only has current and next values (SequenceName.CurrVal and SequenceName.NextVal).

An example of using an Oracle Sequence:

SQL/orc1> Create table UTemp (U_ID Number(10,0), Name VarChar2(30)); Table created.

SQL/orc1> Create Sequence UTemp_SEQ INCREMENT BY 1 start with 1 MAXVALUE 9999999999 NOCACHE;
Sequence created.

SQL/orc1> Insert INTO UTemp (U_ID, NAME) VALUES (UTemp_SEQ.Nextval, 'Mike Rose');
1 row created.

SQL/orc1> select * from UTemp;

     U_ID NAME

--------- ------------------------------
        1 Mike Rose

SQL/orc1> update UTemp Set U_ID = Null; 1 row updated.

SQL/orc1> select * from UTemp;

     U_ID NAME

--------- ------------------------------
          Mike Rose

SQL/orc1> update UTemp Set U_ID = UTemp_SEQ.NextVal 1 row updated.

SQL/orc1> select * from UTemp;

     U_ID NAME

--------- ------------------------------
        2 Mike Rose

In your particular case you can Alter Table Add Column ... Or Create a new table with the new Unique column and you existing columns with the Unique column as its Primary Key, then Insert Into NewTable (U_ID, other column names ...) As Select SequenceName.NextVal, other column names ... From OldTable

Mike Rose

Prasad Chavali wrote in message <7c4gt7$683$1_at_nnrp02.primenet.com>...
>Try this:
>
>Add a column UNQ_ID nuber;
>
>update table_NAME set UNQ_ID = rownum;
>
>This will populate the row with its rownum as the UNQ_ID. If you want you
>can append/convert and do multiple things with the rownum value to generate
>your UNQ_ID.
>
>HTH
>
>Prasad Chavali
>pchavali_at_assettech.com
>
>Sajnish Gupta wrote in message <7c4bee$9le$1_at_mail1.wg.waii.com>...
>>Hi,
>>Is there a way I can create a sequential unique ID in a table that is
>>populated with data.
>>Thank you
>>Sajnish
Received on Tue Mar 09 1999 - 20:24:54 CST