Re: Populating Monotonically-increasing Value in a Column?

From: Brian Peasland <peasland_at_edcmail.cr.usgs.gov>
Date: Fri, 14 Jan 2000 14:25:27 GMT
Message-ID: <387F31D7.C2F0874F@edcmail.cr.usgs.gov>

Try a PL/SQL script:

DECLARE
   start NUMBER;
   increment NUMBER;
   end NUMBER;
   loop NUMBER;
BEGIN

   start:=1;       /* starting value to insert */
   increment:=2;   /* increment value */
   end:=100;       /* highest value to insert */
   LOOP
      EXIT WHEN start > end;
      INSERT INTO table VALUES (start);
      start:=start+increment;

   END LOOP;
END; Syntax may be off, but you get the idea.

HTH,
Brian

GHouck wrote:
>
> 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 - 08:25:27 CST