CODEWITHSUNDEEP
sqloracle-database
jtyler
jtyler

Reputation: 1105

Oracle SQL - update ids in oracle sql to be in sequential order

I have a table in Oracle SQL whose ids are in increasing, sequential order, but there are gaps in the ids due to editing, e.g. the ids are currently something like

I'd like to fix these gaps by just going through each row in the table and update them so there are no gaps. What's the best way to do this?

Upvotes: 2

Views: 9436

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think the following will work in Oracle:

update (select t.*, row_number() over (order by id) as newid) toupdate
    set id = newid

The above answer was accepted a long time ago. It doesn't work. I think the answer should have code that does work, so:

merge into t dest using
       (select t.*, row_number() over (order by id) as newid from t) src
       on (dest.rowid = src.rowid)
   when matched then update set id = newid;

Upvotes: 4

Pasha
Pasha

Reputation: 1655

use this one:

update mytable set id = ROWNUM;

Upvotes: 2

Chris Saxon
Chris Saxon

Reputation: 9825

You can do this with a single SQL statement as follows:

create table t as
  select rownum * 2 id
  from   dual 
  connect by level <= 10;

update t set id = (
  with tab as (
    select id, rownum r 
    from   (select id from t order by id)
  ) 
  select r from tab where t.id = tab.id
);

select * from t;

        ID
----------
         1 
         2 
         3 
         4 
         5 
         6 
         7 
         8 
         9 
        10

This will result in a full scan of t for every row in it, so will be very slow if t is "large". As the commenters have said, think very carefully before doing this; there are better ways to solve this "problem".

Upvotes: 2

Adeel Aslam
Adeel Aslam

Reputation: 1294

I think U Should Execute this code 

DECLARE
   CURSOR A
   IS
      SELECT ROWID DD
        FROM YOUR_TABLE;
   B   NUMBER;
BEGIN
   B := 1;
   FOR I IN A
   LOOP
      UPDATE YOUR_TABLE
         SET COLUMN_NAME = B
       WHERE ROWID = I.DD;
      B := B + 1;
   END LOOP;
END;

Just Replace COLUMN_NAME with your actual column name having wrong ids and then execute this and see the column values which are sorted accuratley.Thanks

Upvotes: 0

Related Questions