how to archive data

[pronate]

hi!

I have a database that traks orders and delivery, about 6 or 7 tables, in a one to many relationship.

Now i want to archive away (take out from the current databse) the old records, based on date, which is on the "one" table. I tried a query, but the result is not updateable...so i cannot delete them off.

I am sure there is a standard and easy way to do this.

Please help

Cheers
Pronate

 

[jrbarnett]

Pronate,

What I would do is to have a separate set of tables with an identical structure to the live copy.

You can then do a couple of queries to copy the data across:

Insert into Archive-one (field1, field2, field3, field4)
select field1, field2, field3, field4
from one
where datefield < Date()

Then

Insert into archive-many (field1, field2, field3, field4)
select field1, field2, field3, field4 from many inner join one on many.field = one.field and one.datefield < Date()

Once this has run, you have the data copied across. Obviously I would put checks in to ensure all the records are copied across, but you can then run the following query:

delete from many inner join one on many.field=one.field where one.datefield < date()

then

delete from one where datefield < date()

Alternatively you can use the relationships to set up cascade delete from one to many table, then get rid of the separate delete from the many table, but you still need the copy routine.

John

 

[pronate]

What if the query is not updatable? I thought id the query involves too many tables and links it is not updatable...

 

[jrbarnett]

Pronate

This is why there are several individual queries that only operate on one table at a time.

John