Home » SQL & PL/SQL » SQL & PL/SQL » populate sequence
populate sequence [message #388925] Thu, 26 February 2009 12:05 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
I have a table whose primary key is col1


create table test(col1 varchar2(20), col2 varchar2(20))

insert into test values('c','d')

insert into test values('q','a')

alter table test add (id number)

create sequence test_seq
start with 1
increment by 1
nocache
nocycle

select * from test


initially the table doesnt have column id, i added id as
the column, which should be a sequence

now, id has to be the pk, but how to populate existing rows
id column ?
Re: populate sequence [message #388928 is a reply to message #388925] Thu, 26 February 2009 12:11 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
create table test(col1 varchar2(20), col2 varchar2(20))

insert into test values('c','d')

insert into test values('q','a')

alter table test add (id number)

create sequence test_seq
start with 1
increment by 1
nocache
nocycle

select * from test


declare
cursor c is
select col1 from test order by 1; -- assuming col1 is existing primary key
begin
for i in c loop
update test
set id = test_seq.next_val
where col1 = i.col1;
end loop;
commit;
end;
/

alter table test add drop primary key
/
alter table test add constraint test_pk primary key (id)
/
Re: populate sequence [message #388930 is a reply to message #388925] Thu, 26 February 2009 12:14 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Congratulations!
Re: populate sequence [message #388941 is a reply to message #388928] Thu, 26 February 2009 13:11 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, although PL/SQL is one possible option to do the job, it is much too complicated. A simple UPDATE does the job efficiently:
UPDATE TEST SET id = test_seq.NEXTVAL;
Previous Topic: Please help
Next Topic: sql_id
Goto Forum:
  


Current Time: Sat Dec 03 20:11:04 CST 2016

Total time taken to generate the page: 0.26655 seconds