CODEWITHSUNDEEP
oracle-databaseoracle11g
XING
XING

Reputation: 9886

Oracle update query to update records 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 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

Solution Provided earlier.

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.

Upvotes: 0

Views: 542

Answers (3)

Pasha
Pasha

Reputation: 1655

I think this would be easiest approach :

update mytable set id = ROWNUM;

Oracle SQL - update ids in oracle sql to be in sequential order

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132580

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

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191275

You could use a merge, but you'd need to join on something other than emp_id as you want to update that column. If there are no other unique columns you can use the rowid:

merge into employee target
using (select rowid, row_number() over (order by emp_id) as emp_id from employee) source
on (source.rowid = target.rowid)
when matched then update set target.emp_id = source.emp_id;

Upvotes: 1

Related Questions