Professional Web Applications Themes

Copying Table Data For Archive - MySQL

Basically, I want to create a system where I am archiving data - copying it from the active tables to the archive tables. I am setting this up so that any user can do it, not just someone technical like me. I am wondering if there is a way to copy multiple data sets with conditions into multiple data tables without doing a loop. Something like this: INSERT INTO archive_a, archive_b SELECT * FROM a, b WHERE b.lid = 10 AND a.tid = b.tid Though this doesn't really work. 'tid' and 'lid' are just identifiers to match up the data ...

  1. #1

    Default Copying Table Data For Archive



    Basically, I want to create a system where I am archiving data -
    copying it from the active tables to the archive tables.

    I am setting this up so that any user can do it, not just someone
    technical like me.

    I am wondering if there is a way to copy multiple data sets with
    conditions into multiple data tables without doing a loop. Something
    like this:


    INSERT INTO archive_a, archive_b SELECT * FROM a, b WHERE b.lid = 10
    AND a.tid = b.tid

    Though this doesn't really work. 'tid' and 'lid' are just identifiers
    to match up the data between tables.

    Any suggestions?

    Thanks,

    GA
    GA Guest

  2. #2

    Default Re: Copying Table Data For Archive

    On Thu, 21 Jun 2007 15:52:41 GMT, GA <com> wrote:
     

    I would archive the data up-front with a trigger that inserts
    every new row that is added to a and b into archive_a and
    archive_b, and tell my users they can delete anything they want
    bcz it is already archived.
     
    --
    ( Kees
    )
    c[_] Recursion: see Recursion (#32)
    Kees Guest

  3. #3

    Default Re: Copying Table Data For Archive

    On Thu, 21 Jun 2007 21:34:53 +0200, Kees Nuyt <demon.nl>
    wrote:
     
    >
    >I would archive the data up-front with a trigger that inserts
    >every new row that is added to a and b into archive_a and
    >archive_b, and tell my users they can delete anything they want
    >bcz it is already archived.
    >[/ref]


    Thanks, that is an interesting suggestion. All of the records
    entered, though, are subject to possible deletion and editing as
    needed.

    I could fix my system to include that for the future, but what I have
    going on now is a bunch of records that need to be archived that are
    already in the tables.

    Since there is a top category for each set of records, I thought it
    would be easy enough to give them the option of archiving current
    records. I would like to do it (as everything) with the least amount
    of code necessary (I'm using php).

    As far as mysql is concerned, do you (or anyone) know of a way to do
    an insert/select in some way like the above? I know I can always pull
    the records and send them through a loop....but if I can do it in a
    one-liner using mysql, I would prefer that.

    -GA

    GA Guest

  4. #4

    Default Re: Copying Table Data For Archive

    On Thu, 21 Jun 2007 20:38:52 GMT, GA <com> wrote:
     
    >>
    >>I would archive the data up-front with a trigger that inserts
    >>every new row that is added to a and b into archive_a and
    >>archive_b, and tell my users they can delete anything they want
    >>bcz it is already archived.
    >>[/ref]
    >
    >
    >Thanks, that is an interesting suggestion. All of the records
    >entered, though, are subject to possible deletion and editing as
    >needed.
    >
    >I could fix my system to include that for the future, but what I have
    >going on now is a bunch of records that need to be archived that are
    >already in the tables.
    >
    >Since there is a top category for each set of records, I thought it
    >would be easy enough to give them the option of archiving current
    >records. I would like to do it (as everything) with the least amount
    >of code necessary (I'm using php).
    >
    >As far as mysql is concerned, do you (or anyone) know of a way to do
    >an insert/select in some way like the above? I know I can always pull
    >the records and send them through a loop....but if I can do it in a
    >one-liner using mysql, I would prefer that.
    >
    >-GA[/ref]

    You can't combine two tables in one
    insert statement, so

    INSERT INTO archive_a, archive_b
    SELECT * FROM a, b
    WHERE b.lid = 10 AND a.tid = b.tid

    indeed won't work.

    A 4 liner would do the job.
    Something like:

    BEGIN TRANSACTION;
    INSERT INTO archive_a
    SELECT *
    FROM a
    WHERE a.tid IN (
    SELECT a.tid FROM a
    INNER JOIN b USING (tid)
    WHERE b.lid = 10
    );
    INSERT INTO archive_b
    SELECT *
    FROM b
    WHERE b.lid = 10;
    COMMIT;

    (untested)
    --
    ( Kees
    )
    c[_] Recursion: see Recursion (#32)
    Kees Guest

Similar Threads

  1. Replies: 0
    Last Post: June 1st, 03:15 AM
  2. Trouble copying data from old table to new table
    By RelentlessMike in forum Coldfusion Database Access
    Replies: 3
    Last Post: May 19th, 05:01 PM
  3. Forms...Copying data from one table to another
    By Paul G. in forum Microsoft Access
    Replies: 2
    Last Post: July 16th, 05:21 PM
  4. Beginner - copying data from one table to another
    By Mark Muller in forum Oracle Server
    Replies: 2
    Last Post: December 6th, 09:28 AM

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