>>I am having a problem with the SQL Update below. From the amount of time it takes to run with only 1 or 2 records in the cursor c_chng I believe it is ignoring the index on k_ivinvt and scanning all 186K recs in the inventory table IVINVT. The last time I had this problem was on updating invoice lines and I fixed it by adding a 2nd line to the where clause to include the invoice key. This time there is no other index I can use. The two tables are tied only by the ivinvt.k_ivinvt = c_chng.k_ivinvt. This has got to be a really common event so I know I am missing something here.
>>
>>I added set enginebehavior 90 but it made no difference
>>
>>
set enginebehavior 90
>>
>>* Update inventory allocated amounts
>>TEXT TO mSqlCommand noshow pretext 15
>> update ivinvt
>> set allocated = allocated + c_chng.chng
>> from c_chng
>> where ivinvt.k_ivinvt = c_chng.k_ivinvt
>>ENDTEXT
>>&mSqlCommand
>
>John,
>Doing it old style help? ie:
>
>select c_chng
>scan
>update ivinvt ;
> set allocated = allocated + c_chng.chng
> where ivinvt.k_ivinvt == c_chng.k_ivinvt
>endscan
>
Cetin
Cetin,
yes that is the way I did it for now. The old Scan Loop never fails. I have been changing all my old code with the scan loops to SQL and I was hoping to use a simple SQL here because I have many places I can use it. When I get some time I will play with it some more.
Thanks.
Beer is proof that God loves man, and wants him to be happy. - Benjamin Franklin
John J. Henn