Accomplish 2 tasks in one pass

#1

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.

Thanks
Lee

#2

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

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Accomplish 2 tasks in one pass

comp.databases.mysql comp.databases.mysql

Accomplish 2 tasks in one pass - 09-24-2006 , 06:32 PM

Re: Accomplish 2 tasks in one pass - 09-25-2006 , 09:49 AM