On Mon, 25 Sep 2006 09:10:05 -0500, "Peter H. Coffin"
<hellsop@ninehells.com> wrote:

>On Sun, 24 Sep 2006 23:32:35 GMT, Lee Peedin wrote:
>> Just wondering if it is possible to perform these 2 steps in one pass.
>>
>>
>> (Step 1 - create an "achive table" and select all rows that meet
>> "mycondition")
>> create newtable select * from oldtable where mycondition
>>
>> (Step 2 - delete the "archived" records from the oldtable)
>> delete from oldtable where mycondition
>>
>> The idea is to create an archive "newtable" and remove the archived
>> data from "oldtable" in one pass.
>
>I'm thinking not. It's two different kinds of operations: INSERT and
>DELETE. So, it's not even possible to take advantage of some kind of
>multi-table feature like a single operation type.
>
>You haven't describe much about what problem you're trying to solve
>here, so I can only guess that it's some kind of archiving operation.
>You MIGHT be able to do something with a "archive this" flag that the
>rest of your processes all respect, which would then leave the flagged
>records immune to other changes, and able to be moved and purged safely.

Thanks Peter,
An archive routine is exactly what I'm trying to solve here. Guess I
could run these 2 steps as a "transaction" and expect Step 2 to "not"
be processed if (for some reason) Step 1 fails.

I'm not concerned that the "created" table will lose such things as
indexes, auto-incr since these archives will never have any additional
rows added to them.

Lee