Generating primary key values after new column has been added to Oracle table

I have a table with 2 varchar2 columns. I have added new number column to existing table to make this column primary key. This table now includes 3 columns. I gave a try to use anonymous block as following:

declare

cnt number;

begin

select nvl(count(*),0) into cnt from sometable;

for i in 1..cnt

loop

update sometable set id=i where i=rownum;

end loop;

end;

Using this anonymous block updates this table unexpectedly.

My solution was to use the following statement:

create table sometablecopy as select row_number() over(order by sometable.col1) as id, sometable.* from sometable;

Nevertheless I am curios why doesn't anonymous block produce expected primary key values with the help of开发者_运维技巧 rownum pseudocolumn? It must be rownum related issue.

Rownum is a pseudocolumn. Its assigned to rows as they are returned from the select. So you can't say "select * from my_table where rownum = 42" since the row with rownum=42 hasn't been defined yet, it will vary depending on your select and predicate (and "select * from my_table where rownum = 1" will return a single row, not the "first" row, whatever that would be). You could do something like (untested):

declare
  cursor sel_cur is
  select rowid as row_id
  from my_table
  order by orderby_col;

  v_ctr pls_integer := 0;
begin
  for rec in sel_cur
  loop
    v_ctr := v_ctr + 1;
    update my_table set pk_col = v_ctr where rowid = rec.row_id;
  end loop;
  commit;
exception
  when others then 
  rollback;
  raise;
end;

This assumes you have sufficient rollback to update the entire table.

Hope that helps.

You cannot use ROWNUM like that (see ROWNUM in SQL).

What you could have done is this:

UPDATE sometable SET id = ROWNUM;