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.

Which version of Firebird? – Mark Rotteveel Jun 29 '17 at 12:32 — Mark Rotteveel, Jun 29 '17 at 12:32

Arioch 'The · Answer 1 · 2017-06-29T12:35:02.307

something like

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

Create a temporary Generator - https://www.firebirdsql.org/manual/generatorguide.html
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
use the GEN_ID function to forward a generator for a specified number of integer units
drop the generator

alternatively use Stored Procedure or 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.

https://www.firebirdsql.org/refdocs/langrefupd25-psql-forselect.html

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

Fill Firebird column with incremental data using Flame Robin

1 Answers1