Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: How to fill empty column with number sequence?

Re: How to fill empty column with number sequence?

From: boa <root_at_localhost.net>
Date: Fri, 13 Feb 2004 23:00:32 GMT
Message-ID: <kGcXb.33039$BD3.7589715@juliett.dax.net>

Ken1 wrote:
> I am going to drop a primary key from one column and create a new
> column to be used as primary key in an existing database.
> The old column was a date column which someone earlier though was a
> good candidate for a primary key which we all know it's not.
>
> Now I want to add a new field, i.e. called ID, with a normal number
> sequence as primary key.
>
> I have dropped the primary key, created the new column, created the
> new sequence and created a trigger to generate the primary key for new
> inserts.
>
> But I don't know how to generate/update the new ID column with
> sequence numbers for the already existing records.
>
> Any ideas?

How about using rownum to generate id's for the existing rows?

alter table foo add bar number;
update foo set bar = rownum;

Remember to create the sequence with the proper "START WITH" number, which should be max(bar) + 1.

HTH
Boa

>
> /Kenneth
Received on Fri Feb 13 2004 - 17:00:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US