CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-updatesequence
anudeepks
anudeepks

Reputation: 1132

Insert unique values after addition of a column in a table

We have a table with two columns and have added another column recently (named sequence_no) , Is there a way to insert unique values like , 1,2,3 for every row in the table ?

eg

table name :   test
desc test
name varchar2
value varchar2
--> n_seq_no number

select * from test
Name    value   n_Seq_no
test1      100
test2      200
test3      300
test4      500

The table already had name, and value as the columns of the table, I need to add unique values for the n_Seq_no column with the existing data,

Output format:

select * from test
Name    value   n_Seq_no
test1      100   1
test2      200   2
test3      300   3
test4      500   4

and so on for all the rows in table.

Upvotes: 0

Views: 1282

Answers (2)

Rusty
Rusty

Reputation: 2138

Assuming that your table is really big it's better to recreate and repopulate:

rename test to old_test;

create table new_test
as
select t.*, rownum as n_seq_no 
  from old_test t
 order by value;

Don't forget to migrate grants, indexes, triggers and etc if any.

UPDATE: ordering is optional. It is required only if you want to assign n_seq_no value using some predefine ordering.

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

You could simply set the new column as ROWNUM.

Something like,

SQL> CREATE TABLE t(
  2  A NUMBER,
  3  b NUMBER);

Table created.

SQL>
SQL> INSERT INTO t(A) VALUES(100);

1 row created.

SQL> INSERT INTO t(A) VALUES(200);

1 row created.

SQL> INSERT INTO t(A) VALUES(300);

1 row created.

SQL>
SQL> SELECT * FROM t;

         A          B
---------- ----------
       100
       200
       300

SQL>
SQL> UPDATE t SET b = ROWNUM;

3 rows updated.

SQL> SELECT * FROM T;

         A          B
---------- ----------
       100          1
       200          2
       300          3

SQL>

If you are on 12c, you could use an IDENTITY COLUMN.

Upvotes: 1

Related Questions