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

    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0

    Firebird 2.5 - add unique ID to each row in a table from stored procedure


    I have a table which doesn't have an unique ID. I want to make a stored procedure which is adding to each row the number of the row as ID, but I don't know how to get the current row number. This is what I have done until now

    CREATE OR ALTER PROCEDURE INSERTID_MYTABLE
    returns (
    cnt integer)
    as
    declare variable rnaml_count integer;
    begin
    /* Procedure Text */
    Cnt = 1;
    for select count(*) from MYTABLE r into:rnaml_count do
    while (cnt <= rnaml_count) do
    begin
    update MYTABLE set id=:cnt
    where :cnt = /*how should I get the rownumber here from select??*/
    Cnt = Cnt + 1;
    suspend;
    end
    end

    Thanks in advance!
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0
    I got an answer on StackOverflow - question Firebird 2.5 - add unique ID to each row in a table from stored procedure :

    Add new nullable column (let's call it ID).
    Create a generator/sequence (let's call it GEN_ID).
    Create a before update/insert trigger that fetches new value from sequence whenever the NEW.ID is null. Example.
    Do update table set ID = ID. (This will populate the keys.)
    Change the ID column to not null.

    A bonus. The trigger can be left there, because it will generate the value in new inserted rows.
    [/QUOTE]

    Can I accomplish this from an SP ?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    If you follow using a generator, a simple:

    sql Code:
    UPDATE mytable SET id=gen_id(yourgenerator, 1)

    will do. No need for a stored procedure.

IMN logo majestic logo threadwatch logo seochat tools logo