Atomic [move to another table, then delete original data]

Question

I have a Data table and an Archive table.

A webapp pours data into the Data table, and a cron job pulls the data out every hour and archives it in the Archive table (this keeps the Data table small and quick to work with).

As I see it there are two psudo-SQL queries to run:

INSERT Archive SELECT * FROM Data;

&

DELETE FROM Data;

However, I only want to delete the data that was successfully copied, if the copy fails I want it to be left in a consistent state, with the data neither half-moved, nor deleted.

What's the right way to do this?

Answer 1

With a transaction.

begin transaction;
insert into archive select * from data;
delete from data where primary_key in (select primary_key from archive);
commit;

Answer 2

I recommend using the multi-table delete syntax, and joining to the archive table for your delete. That way you only delete rows that are in both tables.

Simple Example:

insert into archive select * from data;
delete data.*
from data
inner join archive on archive.id = data.id;

Beyond that, you may want to consider breaking this down into smaller chunks for better performance. For example:

insert into archive select * from data where id > 1000 and id <= 2000;
delete data.*
from data
inner join archive on archive.id = data.id
where data.id > 1000 and data.id <= 2000;

From the manual: http://dev.mysql.com/doc/refman/5.1/en/delete.html