On 27-4-2017 09:03, 'Joje' joje@...
[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