Professional Web Applications Themes

Accomplish 2 tasks in one pass - MySQL

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...

  1. #1

    Default Accomplish 2 tasks in one pass

    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
    Lee Guest

  2. #2

    Default Re: Accomplish 2 tasks in one pass

    On Sun, 24 Sep 2006 23:32:35 GMT, Lee Peedin wrote: 

    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.

    --
    18. I will not have a son. Although his laughably under-planned attempt to
    usurp power would easily fail, it would provide a fatal distraction at a
    crucial point in time.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  3. #3

    Default Re: Accomplish 2 tasks in one pass

    On Mon, 25 Sep 2006 09:10:05 -0500, "Peter H. Coffin"
    <com> wrote:
     
    >
    >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.[/ref]

    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
    Lee Guest

Similar Threads

  1. How Do You Accomplish Leading In a Paragraph with Rules?
    By Ken_Nielsen@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 4
    Last Post: June 30th, 08:53 PM
  2. background tasks without "scheduled tasks"
    By Greg Bryant in forum PHP Development
    Replies: 3
    Last Post: December 7th, 10:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139