Re: DELETE with JOIN

felix@crowfix.com wrote:
> On Thu, Aug 07, 2008 at 03:00:35PM -0400, Frank Bax wrote:
>> If you're really desperate; is it possible to alter table 'a' to add column 
>> b_id; populate it; delete your rows without a join; then drop the column?
> 
> I thought of something similar, but UPDATE has the same limitation as DELETE.


Instead of DELETE; what if you CREATE a new table of the rows you wish 
to keep (using JOIN instead of IN).  Then either drop original table and 
rename new table OR delete all rows and re-populate from new table.

You mentioned that the process of insert/delete is to be repeated.  Are 
all the rows that were inserted; the same ones that will be deleted when 
the cycle is complete?  If yes; then after you delete this batch of 
rows; add a 'junky' column and populate with any value different from 
the default.  Your mass-insert should populate with the default value; 
and you can delete based on this value.