Re: renumbering lines in a table

From: Kenneth C Stahl <kcstahl_at_atl.mediaone.net>
Date: Sun, 03 Sep 2000 03:48:44 GMT
Message-ID: <39B1CA15.6EBFCD6C@atl.mediaone.net>

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
Declare

    RecCount Integer := 0;
    Cursor C1 is
    SELECT *
    FROM TABLE_STUFF
    ORDER BY ITEMNO
    FOR UPDATE OF ITEMNO;
Begin

    For I in C1 Loop

        RecCount := C1%ROWCOUNT;
        If I.ITEMNO != RecCount Then
            UPDATE TABLE_STUFF
            SET ITEMNO = RecCount
            WHERE CURRENT OF C1;
        End if;

    End Loop
End; Received on Sat Sep 02 2000 - 22:48:44 CDT