Re: DELETE with JOIN

felix(at)crowfix(dot)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.