Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

inserting max(col) + 1

user10932986Oct 5 2010 — edited Oct 5 2010
Hi All -

I have trying to do an insert in the following way:
  DECLARE
 
    v_nxt_id                          NUMBER;

 BEGIN
 

  select max(id) + 1 into v_nxt_id from table1;


  For v_loop in (select * from table1)

 LOOP

   insert into table2 (id, col1, col2)
  (select v_nxt_id, col1, col2 

            from table1)

  v_nxt_id := v_nxt_id + 1;


END LOOP;

END
The value of v_nxt_id increments, but the initial value tries to get inserted every time and throws a unique constraint error. What mistake am I doing here. When I try to output only v_nxt_id, I see incremented value
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 2 2010
Added on Oct 5 2010
2 comments
5,261 views