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

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

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

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.

>Thanks,
>
>GA
--
( Kees
)
c[_] Recursion: see Recursion (#32)

On Thu, 21 Jun 2007 21:34:53 +0200, Kees Nuyt <k.nuyt@nospam.demon.nl>
wrote:

>On Thu, 21 Jun 2007 15:52:41 GMT, GA <fang27@excite.com> wrote:
>
>>
>>
>>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?
>
>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.
>


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

On Thu, 21 Jun 2007 20:38:52 GMT, GA <fang27@excite.com> wrote:

>On Thu, 21 Jun 2007 21:34:53 +0200, Kees Nuyt <k.nuyt@nospam.demon.nl>
>wrote:
>
>>On Thu, 21 Jun 2007 15:52:41 GMT, GA <fang27@excite.com> wrote:
>>
>>>
>>>
>>>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?
>>
>>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.
>>
>
>
>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

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)