oracle时间倒序查询,Oracle更新查询以按顺序更新记录

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

Now my query:

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

Updated query:

update (select t.*,

row_number() over (order by emp_id) as newid

from employee t ) toupdate

set emp_id = newid;

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

);

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".