Moving and deleting rows between MySQL tables

I use the following query to move rows from one table to another:

INSERT INTO `new_table` VALUES (SELECT * FROM `old_table` ORDER BY RAND() LIMIT 0,5)

However, I need to delete the rows from the old_table once I move them. Is that pos开发者_如何学运维sible using a similar query to the one above?

Thanks,

joel

no choice

alter table old_table add column selected int(1) not null default 0;
update old_table set selected=1 order by rand() limit 5;
insert into new_table select * from old_table where selected=1;
delete from old_table where selected=1;

or you wrap it as stored procedure, function

Update

When inserting a subset of the data, you will need to follow the INSERT with a DELETE, if you are using InnoDB, wrap it in a transaction so that the action is atomic.

With ORDER BY RAND() you would have a major issue, as you would not know what rows had been deleted. A temporary table to store the target rows would probably be needed.

BEGIN TRANSACTION;

CREATE TEMPORARY TABLE `target_keys`
    SELECT `id` FROM `old_table` ORDER BY RAND() LIMIT 0,5;

INSERT INTO `new_table` VALUES
    (SELECT * FROM `old_table` WHERE `id` IN (SELECT `id` FROM `target_keys`));

DELETE FROM `old_table` WHERE `id` IN (SELECT `id` FROM `target_keys`);

DROP TABLE `target_keys`;

COMMIT;

Pre-Edit Answer

If you are moving all of the rows as in your example, why not just rename the table?

RENAME TABLE old_name TO new_name

If it is a subset of the data, then you will need to follow the INSERT with a DELETE, if you are using InnoDB, wrap it in a transaction so that the action is atomic.