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

Ralf Zwanziger <goldensurfer@gmx.de> wrote in message news:<ims4gv4ccmpsb7eoeh4mu86i2knatu93p6@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

Ralf Zwanziger wrote:

> 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

1. Rename the table
2. CREATE TABLE <original_table_name> AS
SELECT sequence_name.NEXTVAL AS column_alias, field_name, field_name
FROM renamed_table;

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)