CODEWITHSUNDEEP
oracle-databaseplsql
nohat
nohat

Reputation: 7281

Assign auto-incrementing value to new column in Oracle

I have this table in an Oracle DB which has a primary key defined on 3 of the data columns. I want to drop the primary key constraint to allow rows with duplicate data for those columns, and create a new column, 'id', to contain an auto-incrementing integer ID for these rows. I know how to create a sequence and trigger to add an auto-incrementing ID for new rows added to the table, but is it possible to write a PL/SQL statement to add unique IDs to all the rows that are already in the table?

Upvotes: 1

Views: 4492

Answers (4)

Stephanie Abingdon
Stephanie Abingdon

Reputation:

First you should check your PCTFREE... is there enough room for every row to get longer?

If you chose a very small PCTFREE or your data has lots of lenght-increasing updates, you might begin chaining every row to do this as an update.

You almost certainly better to do this as a CTAS.

Create table t2 as select seq.nextval, t1.* from t1.

drop t1

rename t2 to t1.

Upvotes: 0

DCookie
DCookie

Reputation: 43523

Is this what you need?

UPDATE your_table
   SET id = your_seq.nextval;

This assumes you don't care what order your primary keys are in.

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132570

Once you have created the sequence:

update mytable
set id = mysequence.nextval;

Upvotes: 5

Steve
Steve

Reputation: 481

If you're just using an integer for a sequence you could update the id with the rownum. e.g.

update
table
set id = rownum

You then need to reset the sequence to the next valid id.

Upvotes: 3

Related Questions