0atman 0atman - 19 days ago 9
MySQL Question

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

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

With a transaction.

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