Subject Re: [ib-support] how to perform update with aggregate subselect ?
Author Arno Brinkman

> Help me please with this:
> update table1
> set total = (select sum(Quantity) from table2 where table2.CustomerID
> = table1.CustomerID)
> CustomerID is a foreign key for both tables, references to the same
> table "Customers".
> That operator hangs because table2 is large and engine uses natural
> joins. How to work arround the problem?
> Any help would be appreciated.
> Thanks ahead.
> Andrew.

I've tried serveral things (indexes and so on), but FB and IB don't choose a
nice plan.
When you have 30000 and 200000 records it takes 30000 * 30000 * 200000 *
statement_execution_time = TO LONG.
Currently the best way is make an stored procedure which does the same