Fill Firebird column with incremental data using Flame Robin

Question

I have a huge Firebird database with a table that counts 41 millions of rows. Recently I have added a new float column and would like to fill it with incremental data. Each next value should be a previous incremented by RAND(). The very first value is also RAND().

How to do this?

The query

SELECT ID FROM MY_TABLE WHERE MY_COLUMN IS NULL ROWS 1;

takes up to 15 seconds so I wouldn't count on this query executed in a loop.

The table has an indexed ID column which is a part of composite primary key.

Answer 1

Use something like

update MyTable set MyColumn = Gen_ID( TempGen, 
     round( rand() * 100000) ) / 100000.0

1. Create a temporary generator - see the Firebird Generator Guide, and documentation on SEQUENCE (GENERATOR) in the Language Reference.
2. Use the integer generator as your float value scaled by some coefficient, like 100 000 would stand for 1.0 and 10 000 for 0.1, etc
3. Use the GEN_ID function to forward a generator for a specified number of integer units
4. Drop the generator

---

Alternatively use a stored procedure or EXECUTE BLOCK (or https://firebirdsql.su/doku.php?id=execute_block)

Something like

execute block
as
declare f double precision = 0;
declare i int;
begin
  for select ID FROM MY_TABLE WHERE MY_COLUMN IS NULL order by id into :I
   do begin
    f = f + rand();
    update MY_TABLE SET MY_COLUMN = :f where ID = :i;
  end;
end

Or you may try using cursors, but I did not try so I do not know for sure how it would work, see also FOR SELECT.

execute block
as
declare f double precision = 0;
begin
  for select ID FROM MY_TABLE WHERE MY_COLUMN IS NULL order by id 
  as cursor C do begin
    f = f + rand();
    update MY_TABLE SET MY_COLUMN = :f where current of C;
  end;
end