I have a table, say Name, (I simplify the example for simplicity). This table already exists, and has data. Now, I need to add an ID column of type long to it, and need to make the value auto incremented.
For Oracle 12c, this is easy with generated always as identity.
However, I work on Oracle 11g, and such a feature is not supported. So I needed to create a trigger, as described in this post: How to create id with AUTO_INCREMENT on Oracle?
alter table name add (id integer);
create sequence name_seq;
create or replace trigger name_bir;
before insert on name
for each row
begin
select name_seq.NEXTVAL
into :new.id
from dual;
end;
However, that solution (creating a sequence, and then a trigger on Insert/Update) only works when a new row is inserted. It doesn't apply to the existing rows in the table.
I'd appreciate any suggestion. Basically, I need BOTH the existing and the newly-inserted rows to have the new ID values for my newly added column ID.
=============================
Solution (collected from the answer):
Use "update" statement as posted by "a_horse_with_no_name" to update the existing rows
Still need a trigger above for any new rows that will be inserted.
a_horse_with_no_name :
After you created the sequence, just update the existing rows:\n\nalter table name add (id integer);\n\ncreate sequence name_seq;\n\nupdate name \n set id = name_seq.nextval;\ncommit;\n\n\nThere is no need for PL/SQL or a slow and in-efficient row-by-row processing in a LOOP.\n\n\n\nUnrelated, but: the assignment in the trigger can be simplified to: \n\n:new.id := name_seq.NEXTVAL;\n\n\nNo need for a select .. from dual",
2017-01-06T10:14:32
Vishal5364 :
After adding the ID column and creating the NAME_SEQ sequence as you have mentioned, rather that using loop, an easier option would be to use Update statement as follows:\n\nupdate NAME set ID= NAME_SEQ.nextval ;\n",
2017-01-06T10:16:57