-1

I have a table which doesn't have an ID. So I want to add a new column which is 1 to total row number. I opened a new column in the table but I don't know how to fill it with row count. I searched on the Internet but I couldn't find a solution that solves my problem. Can you tell me how to do this? Thanks in advance.

jason
  • 6,962
  • 36
  • 117
  • 198
  • 3
    In what order do you want to generate this id column? You can use sequence if it just needs to be unique (and not neccessarily gapless). You can also use `row_number` while querying instead of storing the value – Gurwinder Singh May 08 '17 at 07:33

2 Answers2

3

You can do this (works for versions 12c or later): 

alter table your_table add id number generated always as identity;

Though, this also generates new values for column, for future insertions.

Also about total row number, this should almost always generate values from 1 to "total row number", but anyway (don't know exactly, when this may work incorrectly), for check this, after alter you can do: select count(*), max(id) from your_table. values should match together.

Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
2

The simplest solution is

update your_table
set id = rownum;

However this won't help you with future insertions to this table. If that is something with is going to happen build a sequence.

SQL> select max(id) from your_table;

   MAX(ID)
----------


SQL> create sequence your_table_seq;

Sequence created.

SQL> update your_table
  2  set id = your_table_seq.nextval;

30 rows updated.

SQL> select max(id) from your_table;

   MAX(ID)
----------
        30

SQL> select id from your_table;

        ID
----------
         1
         2
         3
....
        28
        29
        30

30 rows selected.

SQL>

By the way, now you've added an ID column and populated it be sure to enforce it as a primary key:

alter table your_table add constraint your_table_pk primary key (id);
APC
  • 144,005
  • 19
  • 170
  • 281