MySQL - copy records over to another table and remove existing one

I'm trying to process the following in one sql statement - I want to copy the number of records to the archive table and straight away remove the existing ones from the main table.

Something like:

INSERT INTO `table_archive` 
SELECT * FROM `table_main`
WHERE `id` IN (1, 2, 3, 4)
REMOVE FROM `table_main` WHERE `id` IN (1, 2, 3, 4)

It's basically like cutting and pasting records from one table to another.

Obviousl开发者_如何学Cy the above statement is just what I want to achieve - any idea if it's achievable at all and what sql statement would it be?

as mentioned you need to use several DML commands:

START TRANSACTION;
INSERT INTO `table_archive`  SELECT * FROM `table_main` WHERE `id` IN (1, 2, 3, 4); 
DELETE`table_main` WHERE `id` IN (1, 2, 3, 4); 
COMMIT;

see: http://dev.mysql.com/doc/refman/5.0/en/commit.html

No, you can't do this in a single query. You can, however, try to do it within a transaction, so that it'll be TREATED as a single query. Either everything succeeds, or not happens at all.

This presumes that your tables are of InnoDB type. MyISAM does not support transactions.

Not at all possible. Do a separate DELETE and wrap the entire thing in a transaction (if you are using a language that supports transactions, as opposed to the MYSQL console which I'm not sure supports transactions).