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
