>>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.
>>
>
>Hi John,
>
>The IN(
) is somewhat similar to a join (more than one table is involved) and, as I mentioned before, a join can't be optimized with indexes on a table, which is the target for the UPDATE. The IN (), on the other hand, is a filter condition (only one table is involved) and it can be optimized with an index.
>
>Thanks,
>Aleksey.
I disagree.
IN (SELECT FROM listTable without a WHERE) is not similar to a join, and you ( if want ) can optimize the UPDATE as the IN ()
Of course, if RECCOUNT(listTable) > RECCOUNT(updateTable) the scan of updateTable can to be faster.