Oracle更新查询以按顺序更新记录 [英] Oracle update query to update records 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 
...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之类的分析功能的视图-请参见

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屋！