"Autonumber" insertion in an existing table

[LearnersPermit]

I have an existing table with a large number of records and this afternoon it was determined that the field that I'm currently using as the primary key could be a problem. It was decided that I should put in another field that has an "autonumber".

I have created a sequence and trigger and the next entry will have the autonumber of 1.

My question is: How do I fill in the number for the existing records? Or do I have to fill them in manually - perhaps Aquadata.

Thank you

Have a great weekend.

 

[LKBrwnDBA]

Try:

UPDATE mytable
   SET autonumber = myseq.nextval;

PS: I presume the trigger you created is: "BEFORE INSERT".


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Learners,

You definitely do not need any special application (such as Aquadata) or to manually populate your PK values. You can populate the PK values with one simple SQL UPDATE statement.

The first question is: "Are there any child records in any other table whose Foreign Keys point to the Primary Key of this table?" If not, then here is what I would do to populate the existing rows with values from your sequence:

UPDATE <table_name> SET <PK_Column> = <sequence_name>.nextval;

This one statement will re-populate all values in your table's PK column with whatever are the next values from your sequence.

If there are existing FK references to the current values of your new PK column, then adjust the UPDATE code to read:

UPDATE <table_name> SET <PK_Column> = <sequence_name>.nextval
WHERE <PK_Column> IS NULL;

Let us know if this scenario differs from your situation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[LearnersPermit]

Thank you LKBrwnDBA and SantaMufasa your suggestions worked perfectly. My autonumber was populated quickly.