MySQL - move a subset of a table into another table

I would like to move old records from a table to another one.

If I do this:

INSERT INTO archived_item SELECT * FROM item WHERE is_sold=1 AND sold_at < DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)

I would just copy rows. But I need to move them.

I don't think running this query after the prev开发者_运维知识库ious one is ideal:

DELETE FROM item WHERE is_sold=1 AND sold_at < DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)

I wouldn't like to use any external tool either.

Is it possible to achieve that via SQL?

As far as I know, the only way to do this is to run the INSERT query and then run a delete query on the source table. There is no other way to achieve it.

As far as I know, it's not possible. You will have to explicitly issue a delete command if you want to remove data from the old table.

There is nothing wrong in doing the two commands together. It is a proper way to do what you want.

To guarantee that you are actually deleting only the ones that you have moved you need to wrap the the commands in a transaction.

Now, the only thing that you can still question is the actual decision to have different tables for archive and working set.

There could be two reasons for this:

• performance
• different data integrity rules applied to the two sets

Performance alone should not be an issue, with proper indexes, until you hit some very, very large number of records. And it will increase complexity of your user interface, reporting which translate to maintenance (as stated above - if different rules apply to archive records, or if the structure of the data is not the same, justification is easier).