Re: renumbering lines in a table

You must create new sequence starting with 1 add on your table trigger with numbering like this

create trigger tgr_A before update on table A begin
  select NEW_SEQUENCE.nextval into :new.itemno from dual; end;

after that start sql statement like this update A set itemno=itemno;

In all statements A is your tablename

In article <8op110$bge$1_at_nnrp1.deja.com>,   martincat_at_my-deja.com wrote:
> If I have a table TABLE_STUFF like this simple example using two
> columns to illustrate:
>
> ITEMNO THINGS
> 1 APPLES
> 2 GRAPES
> 3 PICKLES
> 4 PEARS
>
> And I do this delete:
>
> DELETE from TABLE_STUFF things = ‘GRAPES’;
>
> At this point if I do a SELECT * from TABLE_STUFF I see this with a
> gab between 1 and 3:
>
> ITEMNO THINGS
> 1 APPLES
> 3 PICKLES
> 4 PEARS
>
> Question: How can I easily update the content of itemno, so my table
> can again look like this (below) where the itemno reflects the actual
> line position in the table when I do a SELECT * from TABLE_STUFF:
>
> ITEMNO THINGS
> 1 APPLES
> 2 PICKLES
> 3 PEARS
>
> Thanks.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Sep 02 2000 - 07:56:55 CDT