0

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):

  1. Use "update" statement as posted by "a_horse_with_no_name" to update the existing rows

  2. Still need a trigger above for any new rows that will be inserted.

Community
  • 1
  • 1
Simo
  • 2,292
  • 5
  • 30
  • 45
  • Create a `#table_seq` and just write a query that will `loop` through `#table` by adding `#table_seq.nexval` to `id` column – kzharas210 Jan 06 '17 at 09:14
  • you meant, I need both the trigger (for the newly inserted rows), and the loop (for the existing rows), correct? – Simo Jan 06 '17 at 09:16
  • You don't need any trigger. Just use `#table_seq.nextval` while inserting new row – kzharas210 Jan 06 '17 at 09:18
  • I am not well familiar with PL/SQL. Can you give the syntax example? – Simo Jan 06 '17 at 09:22
  • @kzharas210: there is no need for a slow loop in PL/SQL to update the existing rows. –  Jan 06 '17 at 10:15

3 Answers3

4

After you created the sequence, just update the existing rows:

alter table name add (id integer);

create sequence name_seq;

update name 
  set id = name_seq.nextval;
commit;

There is no need for PL/SQL or a slow and in-efficient row-by-row processing in a LOOP.

Unrelated, but: the assignment in the trigger can be simplified to: 

:new.id := name_seq.NEXTVAL;

No need for a select .. from dual

-1

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:

update NAME set ID= NAME_SEQ.nextval ;
Vishal5364
  • 293
  • 1
  • 4
  • 21
-2

This should do the trick

BEGIN
alter table name add (id integer);
create sequence name_seq;

FOR REC IN (SELECT * FROM NAME) LOOP
UPDATE NAME SET ID = NAME_SEQ.NEXTVAL 
WHERE ROWID = REC.ROWID;
END LOOP;
END;
monkeyStix
  • 620
  • 5
  • 10