On 27-4-2017 09:03, 'Joje' j...@codework-solutions.com [firebird-support] wrote: > 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 > [..] > > - 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.
You should try MERGE instead of that EXECUTE BLOCK, it is likely (but not guaranteed) to perform better. eg something like: merge into table_b as b using table_a as a on b._id = a._id and b.ar_id = a.ar_id and b.ref_a_id is null when matched then update set b.ref_a_id = a.ref_a_id See https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-merge.html How well that really performs depends on the presence of a suitable index in table_b. -- Mark Rotteveel