Oracle SQL-将Oracle SQL中的ID更新为顺序 [英] Oracle SQL - update ids in oracle sql to be in sequential order

问题描述

我在Oracle SQL中有一个表，其ID的顺序是递增的，但是由于编辑(例如，这些ID当前类似于

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
• ...等

我想通过遍历表中的每一行并更新它们来解决这些差距，因此没有差距.最好的方法是什么?

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?

推荐答案

我认为以下内容适用于Oracle:

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;

这篇关于Oracle SQL-将Oracle SQL中的ID更新为顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！