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
  • [firebird-support... 'Joje' j...@codework-solutions.com [firebird-support]
    • Re: [firebir... Mark Rotteveel m...@lawinegevaar.nl [firebird-support]
    • Re: [firebir... Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]

Reply via email to