>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

Hi John,

For SQL-UPDATE command, the target table is always used as the first in the join. Therefore, its indexes can't be used to optimize the join, but they can be used to optimize filter conditions.

Thanks,
Aleksey.