Thanks to all the people that helped.
This is the way I decided to write it. Not the most elegant way but runs really fast; .04 seconds to update 200 records. I am building a string of keys. mk_ivinvt holds "AAAAA","BBBBB", etc. up to 120 keys at a time.
My question now is: Why the heck does
IN (SELECT K_IVINVT FROM C_CHNG)
take the same 12-15 seconds as the original query.
Where using
in (<<mk_ivinvt>>)
take only .04 seconds???? It seems they should both be the same but then, as we all know, software works in strange and mysterious ways.
set enginebehavior 90
mseconds = seconds()
mrecno = 1
use c_chng
do while not eof('c_chng')
mk_ivinvt = ''
mcnt = 1
select c_chng
goto mrecno
scan while mcnt <= 120
if empty(mk_ivinvt)
mk_ivinvt = mk_ivinvt + '"' + k_ivinvt + '"'
else
mk_ivinvt = mk_ivinvt + ',"' + k_ivinvt + '"'
endif
mcnt = mcnt + 1
endscan
mrecno = recno()
TEXT TO mSqlCommand noshow pretext 15 TEXTMERGE
update ivinvt
set allocated = allocated + c_chng.chng
from ivinvt, c_chng
where c_chng.k_ivinvt = ivinvt.k_ivinvt
and ivinvt.k_ivinvt in (<<mk_ivinvt>>)
ENDTEXT
&mSqlCommand
enddo
wait window nowait transform(seconds() - mseconds, '99.999')
Beer is proof that God loves man, and wants him to be happy. - Benjamin Franklin
John J. Henn