Populating Monotonically-increasing Value in a Column?

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 Thu Jan 13 2000 - 18:33:39 CST