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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.