1

I have an Oracle table like this:

fruit    id
-------- -----
apple     1
plum      9
pear      55
orange    104
..

The id column numbers are wrong. How can I update the id of each row to be re-sequenced like this: 

fruit    id
-------- -----
apple     1
plum      2
pear      3
orange    4

What's the most efficient way to do this?

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
user648244
  • 1,240
  • 7
  • 26
  • 41
  • http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle – Matt Busche Apr 08 '13 at 16:06
  • Hi, i dont want to delete the table and recreate it. I just want a normal loop to reset/increment – user648244 Apr 08 '13 at 16:07
  • Make a procedure fetch data in ascending order and update each value. Or if it is auto increment refer this http://stackoverflow.com/questions/5593623/reset-pk-auto-increment-column – commit Apr 08 '13 at 16:14
  • 3
    Step 1 - justify the requirement. If id is your primary key, updating it might be a bad idea. – Dan Bracuk Apr 08 '13 at 16:22
  • 1
    @DanBracuk - indeed; if this is just to get rid of gaps for display, for example, then using `rownum` or `row_number()` at that point will be easier, safer and quicker than trying to keep contiguous ID values maintained. A synthetic key should be immune to such concerns. – Alex Poole Apr 08 '13 at 16:28
  • Please explain why the IDs are "*wrong*". If that is the primary key, there is absolutely nothing wrong with them. –  Apr 09 '13 at 14:26
  • Sorry for the confusion but the 'id' is not a primary key. It is just a column with random numbers. – user648244 Apr 09 '13 at 14:30

3 Answers3

3
update your_table
set id = rownum
Egor Skriptunoff
  • 23,359
  • 2
  • 34
  • 64
  • order by id first. This will break if there's a unique contraint and somehow further down the rows there's a fruit with id=2. – 000 Apr 08 '13 at 16:14
  • @JoeFrambach - Where do you want to insert `order by`? Please write your full query. – Egor Skriptunoff Apr 08 '13 at 16:25
  • 1
    @JoeFrambach - no it won't it's an atomic operation, at the end of which there will still only be one row with ID 2. It will assign the new IDs in a random(ish) order, but it's not clear if that matters. – Alex Poole Apr 08 '13 at 16:25
  • This will not necessarily keep the old order (might or might not be a problem). –  Apr 09 '13 at 14:26
1

If you need to guarantee the old order, the following should do it:

merge into the_table 
using
( 
   select rowid as rid, 
          row_number() over (order by id asc) as new_id
   from the_table
) t on (t.rid = the_table.rowid) 
when matched then 
  update set id = new_id;
0
DECLARE
i INTEGER :=1;
BEGIN;
    FOR n IN (SELECT your_primary_id  FROM your_table_name ORDER BY your_primary_id);
    LOOP;
        UPDATE your_table_name 
          SET your_primary_id=i 
          WHERE your_primary_id = n.your_primary_id;
        i := i + 1;
    END LOOP;
END;
/
slavoo
  • 5,798
  • 64
  • 37
  • 39
chandanmishra
  • 1