CODEWITHSUNDEEP
sqldatabaseoracle-databaseauto-incrementrownum
towi
towi

Reputation: 22307

Is the use of rownum safe to update a table with incremented IDs on Oracle?

Similar to MySql I want update (fill) an empty column with incremental values -- with Oracle. I.e. after

ALTER TABLE data ADD
(
  id  number
);

I want for all records the ID column to receive unique values. After that I will enable Not Null and unique constraints to make it a primary key.

I came up quickly with 

UPDATE TABLE data SET id = rownum;

but I have a bad feeling about this. It works in my tests as expected, but an example is no proof :-)

Is it safe to use rownum in this manner in an update-statement?

Upvotes: 2

Views: 2816

Answers (3)

Art
Art

Reputation: 5792

No, it is not safe, as ROWNUM is pseudocolumn. Means it does not guarantee the sequence especially when using with ORDER BY. If you must, then use ROW_NUMBER() OVER (ORDER BY your_field) instead of ROWNUM. You may also use PL/SQL to update your table once in the loop.

Upvotes: -3

David Aldridge
David Aldridge

Reputation: 52376

Yes, I've never had a problem with that method, but enabling not null and unique constraints does not make it a primary key -- adding a primary key constraint makes it a primary key ;)

Upvotes: 4

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23757

Probably, your method is safe. If not, you would be notified if unique constraint fails :-).

Bullet-proof method is the following: 

lock table data in exclusive mode;

merge into data t
using (select t.rowid rid, t.rownum id from data t) s
on (t.rowid = s.rid)
when matched then update set
  t.id = s.id;

commit;

Upvotes: 0

Related Questions