Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to add a primary key column

Re: How to add a primary key column

From: <sybrandb_at_yahoo.com>
Date: 2 Jul 2003 02:10:54 -0700
Message-ID: <a1d154f4.0307020110.7655b046@posting.google.com>


Ralf Zwanziger <goldensurfer_at_gmx.de> wrote in message news:<ims4gv4ccmpsb7eoeh4mu86i2knatu93p6_at_4ax.com>...
> I have a Table without a primary key and want to add a primary key.
> The problem is: how to update the existing columns with unique number
> values? I have the table with a column PK_Table and a sequence
> SEQ_Table. I thought the following statement would do it:
>
> Update Table set PK_Table = (select SEQ_Table.NextVal from dual)
>
> but this only results in ORA-02287: Sequence not allowed here.
> Any idea how to solve the problem, maybe with a cursor in PL/SQL?
>
> Thanks,
> Ralf

CURSOR FOR LOOP
BEGIN
for i in (select * from table for update of pk_table) loop update table
set pk_table = (select seq_table.nextval from dual) where current of i;
end loop;
end;
/

and that is all. Homework!

Sybrand Bakker
Senior Oracle DBA Received on Wed Jul 02 2003 - 04:10:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US