Hi,
Please help me to solve the following (seemingly simple) problem :)
I have a table with a number field that specifies the order of the row, for example:
0
1
2
3
4
5
Now, some rows are deleted, so now there are gaps in the numbers:
0
1
4
5
I would like to renumber these rows, based on the following requirements:
<li>the numbering must be contiguous, starting with 0
<li>the relative order of rows needs to be respected
<li>only those rows that need to be renumbered may be updated
For the above example, only the rows with numbers 4 and 5 should be updated and changed into 2 and 3.
Of course it's easy to write a cursor loop that does this, but this is not what I'm looking for.
Can this be done in
one update statement?
I was trying to abuse the merge statement for this, but it does not allow for "when _not_ matched then _update_":
merge into mytable t
using ( select num, rownum-1 newnum from mytable
order by num ) n
on ( t.num = n.newnum )
when not matched then update set t.num = n.newnum;
Thank you very much!
Edited by: Pleiadian on Jun 15, 2010 10:47 AM