Re: Deleting rows using key from a 2nd table

In article <412p06$7fs_at_ixnews7.ix.netcom.com>, chuckh_at_ix.netcom.com (Chuck Hamilton) writes:

>I have a job where there's a very large table, and I want to delete a
>small number rows out of it based on data obtained from a much smaller
>table. I would normally using something like this...
>
>DELETE FROM big_table a WHERE EXISTS
>(SELECT 'x' FROM small_table b
> WHERE a.pkcol_1 = b.pkcol_1 AND
> a.pkcol_2 = b.pkcol_2
>)
>
>By looking at the statement though, it looks like it's going to try
>and go through the entire big_table which may contain millions of
>rows. Since the small table will only contain about 50,000 rows, of
>which only maybe 100 will exist in big_table and actually get deleted,
>isn't there a better way? Would using "IN" instead of "EXISTS" work
>better in this case? I've never seen a case where it did. How would
>you even use "IN" when there's more than one column involved?
>
>TIA
>
>Please respond via email if possible.
>--
>Chuck Hamilton
>chuckh_at_ix.netcom.com
>
>Never share a foxhole with anyone braver than yourself!

How about...

DELETE FROM big_table a
WHERE (pkcol_1, pkcol_2) = (SELECT pkcol_1, pkcol_2 FROM small_table);

James Richard
(JRichard_at_TUNS.CA, http://www.tuns.ca/~jrichard/) "No Assembler Required!" Received on Wed Aug 23 1995 - 00:00:00 CEST