How to resequence an oracle table id

Question

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?

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
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
@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. — a_horse_with_no_name, 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

score 3 · Accepted Answer · answered Apr 08 '13 at 16:11

3

update your_table
set id = rownum

answered Apr 08 '13 at 16:11

Egor Skriptunoff

22,353
2
33
62

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. – Joe Frambach 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). – a_horse_with_no_name Apr 09 '13 at 14:26

a_horse_with_no_name · Answer 2 · 2013-04-09T14:49:34.950

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;

edited Apr 09 '13 at 14:49

answered Apr 09 '13 at 14:42

a_horse_with_no_name

497,550
91
775
843

score 0 · Answer 3 · edited Apr 04 '14 at 07:01

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;
/

edited Apr 04 '14 at 07:01

slavoo

5,437
64
35
39

answered Apr 04 '14 at 06:32

chandanmishra

1

How to resequence an oracle table id

3 Answers3