jtyler jtyler - 1 year ago 104
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?

Answer Source

I think the following will work in Oracle:

update (select t.*, row_number() over (order by id) as newid) toupdate
    set id = newid
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download