In my apps, IDs like this are always integers > 0. I often have to add
what I call hints to queries like this that don’t want to use the
primary key without a little nudging:

update table1
  set total = (select sum(Quantity) from table2 where table2.CustomerID
= table1.CustomerID and table2.CustomerID > 0)

Maybe it works in your case, maybe not…

Louis Kleiman
SSTMS, Inc.


-----Original Message-----
From: Andrew Guts [mailto:andr@...]
Sent: Wednesday, June 19, 2002 8:53 AM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] how to perform update with aggregate subselect
?

Martijn Tonies wrote:

>Hi,
>
> 
>
>>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?
>>   
>>
>
>What is the query plan?
>
> 
>

PLAN (T2 NATURAL)
PLAN (T1 NATURAL)




To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.