Assign auto-incrementing value to new column in Oracle

Question

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?

Answer 1

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.

Answer 2

Once you have created the sequence:

update mytable
set id = mysequence.nextval;

Answer 3

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.

Answer 4

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.