Re: delete based on multipart key

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 thought that you couldn't reference the same table in a > subquery of a delete or update. Also it seems like the 'first_table' > in the subquery has no join to the 'first_table' in the main > 'delete from' clause. Is this a bug, a feature, or both (or are > David and I just ignorant? :) ) I'll have to try it though. > > Douglas Wilson I think that should be a correlated sub-query, as follows delete from first_table where 1 in (select 1 from second_table where first_table.a = second_table.a and first_table.b = second_table.b) This can be really slow, because it will do a sequential scan on first_table(probably why query was first posted). Therefore I use an IN with a non-correlated sub-query as has already been suggested by Rudy. Interesting aside: If query looked as follows: delete from first_table where 1 in (select 1 from second_table where first_table.a = second_table.a and first_table.b = second_table.b AND second_table.c = "SOMEVALUE") and second_table.c had an index, I think Oracle would be faster than Informix because I think it would do an indexed read on second_table and then an indexed delete on first_table, whereas Informix would still start from the outside and do a sequential scan on first_table. Any comments? Greg