Re: Creating IDs subsequently

Originally posted by Gerd Forster
> Hello everybody,
>
> I have a table with timestamp and other information.
> Recently I added a numeric ID column.
>
> I want to fill in IDs in CHRONOLOGIC ORDER.
>
> Is this possible by an update statement, or do I have to write
> a procedure?
>
> Thanks in advance
> Gerd Forster

If your table has an existing unique key (possibly composite), then you could do this:

update mytable
set id =
( select rn from
  (
  select unique_key, row_number() over (order by timestamp_col) rn   from t
  ) v
  where t.unique_key = v.unique_key
);

Without an existing unique key to work with, I don't see how you can do it in a single update.

--
Posted via http://dbforums.com