0

Oracle 10g

I have a table that has a compound key, which I want to replace with a singular key. So I've added an id column. Now I need a single update statement update existing data.

Example:

MyTable(NewID,CMP_Key1,CMP_Key2)
NULL,1,1
NULL,1,2
NULL,2,2
NULL,2,2

Needs to be updated to:

1,1,1
2,1,2
3,2,2
4,2,2

What I've tried so far:

Update MyTable SET NewID = (SELECT ROWNUM FROM DUAL);

Which doesn't work. This will set them all to 1.

wvdz
  • 16,251
  • 4
  • 53
  • 90

2 Answers2

2

You can just do:

update MyTable set NewId = rownum;

SQL Fiddle.

But presumably you'll want to increment the NewId column for future inserts, quite likely with a sequence and maybe a trigger. You'd need to make the sequence start with the highest value you set manually (i.e. the number of rows in the table when you run the update), so you might as well just use the sequence here:

create sequence MyTableSeq;
update MyTable set NewId = MyTableSeq.nextval;

SQL Fiddle.

Both assume this is a purely synthetic key and you don't want to impose any ordering as it's generated.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thx. In my actual situation I have a table that keeps track of the sequence, so my actual query will be something like `UPDATE MyTable SET NewID = ROWNUM + (SELECT Seq FROM Seq_Table WHERE Name = 'MyTable');` Which works :) – wvdz Feb 19 '14 at 11:04
  • Why would you want a separate table to keep track of the sequence, rather than just using a native sequence? Having to refer to and update that table will serialise your inserts? – Alex Poole Feb 19 '14 at 11:11
  • I'm customizing an existing ERP system. I'm not sure why they chose to have a separate table to keep track of the sequence. – wvdz Feb 19 '14 at 11:48
0

Try this one 

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

commit;
Srini V
  • 11,045
  • 14
  • 66
  • 89