Re: delete based on multipart key

In <5ickgb$nqk$2@gulfa.kuwait.net>, Rudy Fernandes wrote: >In article <334330B8.4D64@gte.net>, Douglas Wilson < > wrote: >>David Williams wrote: >>(snip) >>> delete from first_table >>> where 1 in >>> (select 1 from first_table,second_table >>> where first_table.a = second_table.a and >>> first_table.b = second_table.b)>>> >>(snip) >>> Somehow though I still don't feel I totally understand WHY it >>>works... >> >I don't understand it either, David, but it still does a SEQUENTIAL >SCAN of first_table. > >Why? Absolutely no clue. > Informix does not allow modification on table/view used in subquery, so I don't see how this delete will work. If you are worried about the sequential scan, we will change delete to select. select ... from first_table where 1 in (select 1 from first_table,second_table where first_table.a = second_table.a and first_table.b = second_table.b) will do a sequential scan for first_table in "in (select ....)" If you change the select as below : select ... from first_table where 1 in (select 1 from second_table -- Notice the difference in from .... where first_table.a = second_table.a and first_table.b = second_table.b) will always use the index Cheers Felix K. Mathews fmathews@systems.dhl.com