MySQL Cookbook

Moving Records Between Tables Safely

Problem

You’re moving records by copying them from one table to another and then deleting them from the original table. But some records seem to be getting lost.

Solution

Be careful to delete exactly the same set of records from the source table that you copied to the destination table.

Discussion

Applications that copy rows from one table to another can do so with a single operation, such as INSERT ... SELECT to retrieve the relevant rows from the source table and add them to the destination table. If an application needs to move (rather than copy) rows, the procedure is a little more complicated: After copying the rows to the destination table, you must remove them from the source table. Conceptually, this is nothing more than INSERT ... SELECT followed by DELETE. In practice, the operation may require more care, because it’s necessary to select exactly the same set of rows in the source table for both the INSERT and DELETE statements. If other clients insert new rows into the source table after you issue the INSERT and before you issue the DELETE, this can be tricky.

To illustrate, suppose you have an application that uses a working log table worklog into which records are entered on a continual basis, and a long-term repository log table repolog. Periodically, you move worklog records into repolog to keep the size of the working log small, and so that clients can issue possibly long-running log analysis queries on the repository without blocking ...