Oracle 更新查询以按顺序更新记录 [英] Oracle update query to update records in sequential order

问题描述

我在 Oracle SQL 中有一个表，它的 id 是按递增顺序排列的，但是由于编辑，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 
...etc

我查看了一个帖子，提供的解决方案如下:

I check one post and the solution provided was as below:

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

之前提供的解决方案.

现在我的查询:1) 我猜上面的查询中缺少From 子句".

Now my query: 1) I guess the "From clause" is missing in the above query.

更新的查询:

update (select t.*, 
              row_number() over (order by emp_id) as newid 
       from employee t ) toupdate 
set emp_id = newid; 

2) 当我运行上面的查询时，它给了我错误此视图上的数据操作操作不合法".

2) When i run the above query, it gives me error "data Manipulation operation not legal on this view".

谁能解释上述解决方案在这里的工作原理.任何人都可以发布完整的更新查询.谢谢.

Can anyone explain how the mentioned solutions worked here. can anyone post the full update query. Thanks.

推荐答案

此解决方案您引用的同一问题显示怎么做:

This solution to the same question you referenced shows how to do it:

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

那行得通.您不能更新包含像 row_number 这样的分析函数的视图 - 请参阅 Oracle 12C 文档，查找关于可更新视图的注释".

That works. You cannot update a view that contains an analytic function like row_number - see Oracle 12C docs, look for "Notes on Updatable Views".

这篇关于Oracle 更新查询以按顺序更新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！