Thread: Numbering rows

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    14

    Numbering rows


    Hi,


    is there a way to insert numbering into a table WITHOUT USING A STORED PROCEDURE?


    like i have an existing table and i want the NR column have values from 1 to N (the number of rows in the table)


    Can i do that with a simple UPDATE command? How?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    224
    Rep Power
    12
    You can use EXECUTE BLOCK to write a code just like stored procedure and execute it once or you can write a trigger active before update/insert + generator. If you write a trigger it will help you with the future records when you insert them.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    14
    Thank you for your answer.

    That sounds really good. Could you please provide an example? I never used that EXECUTE BLOCK thingy.

    Let's say this is the table structure:

    NR (integer)
    DATA (anything)


    How would the numbering block look like, and how can i execute it?


    PS: The trigger+generator is not an option here because i often need to renumber a filtered set of records.
    Last edited by nagysz; October 25th, 2013 at 01:41 AM.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    224
    Rep Power
    12
    You can execute Execute block as you execute any other select statement.
    It's just a block of PSQL code in normal query.
    In Delphi I use TIBSQL and its ExecQuery method.
    Code:
    EXECUTE BLOCK
    AS
    DECLARE VARIABLE L_COUNTER INTEGER;
    DECLARE VARIABLE L_PRIMARY_KEY PRIMARY_KEY_TYPE;
    BEGIN
        L_COUNTER = 1;
    
        FOR SELECT
           PRIMARY_KEY
        FROM
           TABLE
        INTO
           :L_PRIMARY_KEY
        DO BEGIN
    
           UPDATE TABLE SET
               NR = :L_COUNTER
           WHERE
               PRIMARY_KEY = :L_PRIMARY_KEY;
    
           L_COUNTER = L_COUNTER + 1;
    
        END
    END

    Comments on this post

    • nagysz agrees
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8
    Or you can use this code into a trigger_BI:

    DECLARE VARIABLE L_COUNTER INTEGER;
    BEGIN
    select max(nr) from your_table_here into :lcounter;
    new.nr = lcounter+1;
    END
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    224
    Rep Power
    12
    In upper post @nagysz said that the trigger is not an option.

IMN logo majestic logo threadwatch logo seochat tools logo