CODEWITHSUNDEEP
firebirdfirebird2.5
RBA
RBA

Reputation: 12584

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

Upvotes: 3

Views: 1862

Answers (1)

cincura.net
cincura.net

Reputation: 4150

I think better way will be:

  1. Add new nullable column (let's call it ID).
  2. Create a generator/sequence (let's call it GEN_ID).
  3. Create a before update/insert trigger that fetches new value from sequence whenever the NEW.ID is null. Example.
  4. Do update table set ID = ID. (This will populate the keys.)
  5. 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.

Upvotes: 5

Related Questions