jtyler jtyler - 2 months ago 12x
SQL Question

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

  • 22

  • 23

  • 24

  • 32

  • 33

  • 44

  • ...etc

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?


I think the following will work in Oracle:

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