<p>I have 2 tables, an active table and an inactive table. I want to <em>move</em> rows from the active to the inactive table. My first thought was</p>

<pre><code>insert into inactive select * from active where ...
delete from active active where ...
</code></pre>

<p>However about .42 seconds later I noticed this will drop/duplicate rows if updates alter what the where clause selects.</p>

<p>In this case, I can easily prevent that but what should I do in cases where I can't?</p>

<p>edit: From the answers it look like there isn't an easy/trivial way to do this. I'm really surprised by this. I would think that there would be some substantial benefits to having it.</p>

Status flags are your friend.
```
```
UPDATE old_data SET move="MARKED";
INSERT INTO somewhere... SELECT where move="MARKED";
DELETE FROM old_data WHERE move="MARKED";
```
```
If you do this with Autocommit off, it will seize locks all over the place.
You can COMMIT after each step, if you want to do a little less locking.