Hello everyone,

Is there anyway using which we could perform bulk update in firebird.

 I have two tables A & B. Both tables are referenced by a varchar(100) AR_ID column. 

-          Total number of rows in each table is around 1 million. Now in order to get faster result I created a bigint column ref_A_ID in Table B that is referenced to A_ID column of table A.

-          Now, to reference existing data in table B. I want to update column ref_A_ID of table B with value of A_ID of table A for proper linking to table A.

-          I created a query for updating table B is as below

-          EXECUTE BLOCK

-           AS

-           DECLARE VARIABLE REF_A_ID BIGINT;

-           DECLARE VARIABLE AR_ID VARCHAR(200);

-           DECLARE VARIABLE _ID BIGINT;

-           

-           BEGIN

-           FOR    SELECT   A. AR_ID,   A.A_ID,   A. _ID  

-                            FROM  TABLE  A

-                            INNER  JOIN   TABLE  B   ON   B. AR_ID   =  A. AR_ID

-                            INTO     :AR_ID,    : REF_A_ID,    :_ID

-              DO

-                BEGIN

-                          UPDATE   TABLE    B

-                          SET    B. REF_A_ID    =    : REF_A_ID

-                          WHERE   ( B. REF_A_ID    IS    NULL  AND  B. _ID = :_ID)  AND       (B. AR_ID = :AR_ID);

-                END

-           END       

-          Is there a way through which this query can be further optimised as this query taking time for completion more than 3 -4 hours.  Could I use batch based update in this query…?

Thanks.

With Regards,

Joje