Re: Populating Monotonically-increasing Value in a Column?

You could try something like this from
SQL*Plus, but you need to stop other
users messing up the data whilst it
goes on.

/* Create and populate a demo table */ drop table temp1;

create table temp1 nologging
as
select rownum id,object_name
from all_objects
where rownum <= 50;

update temp1 set id = 0 where id > 20;

commit;

select id from temp1;

/* How to do the update */

column id new_value m_id
select max(id) id from temp1;

update temp1 set id = rownum + &m_id
where id = 0
;

select id from temp1;

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

GHouck wrote in message <387E6EE3.320C_at_teleport.com>...
>Is there a simple/standard method for doing a one-time population
>of a numeric column, with a monotonically-increasing value, that
>also starts at a value that is greater than already existing values
>in the column?
>
>That is, if I have a column (NUM) that has some zero and non-zero
>values, and I want to update the zero values with new, positive
>values that are (at least) larger than all of the existing
>non-zero values:
>
>Table:
>
> NUM
> ---
> 1
> 2
> 3
> 0
> 0
> 0
>...
>
>And I want as a result:
>
> NUM
> ---
> 1
> 2
> 3
> 4
> 5
> 6
>...
>
>Logically, I would like to perform something like this:
>
>update
> mytable
>set
> num=(select max(num)+1 from mytable) where num=0;
>
>This DOES work, however only uses the original max(num), so
>all values are updated to the same number (i.e., max(num)+1).
>
>Thanks,
>
>Geoff Houck
>systems hk
>hksys_at_teleport.com
>http://www.teleport.com/~hksys
Received on Fri Jan 14 2000 - 09:02:00 CST