>I have an original table (table1) and a 'delete reference' table (table2). I want to delete everything in table1 where there is a definitive match and also an 'X' in table2. So I have this:
>
>delete from table1 ;
>where ;
>table1.WhatComp = table2.cWhatComp ;
>and table1.LastName = table2.cLastName ;
>and table1.Assoc_ID = table2.cAssocID ;
>and table2.cDelete = 'X'
>
>However, I have to run this 10,000 times to delete everything in table1. I know I can wrap a scan...endscan loop around this, but I am wondering if there is a "pure SQL" way to delete everything from table1 based on table2 criteria?
>
>Thanks.
What you have are 3 JOIN conditions and 1 Filter condition. The easiest way is to come up with a simple test:
delete from table1 where table1.someid in (select table2.someid where table2.cDelete = 'X')
Mark McCasland
Midlothian, TX USA