Migrating rows to a new table and then deleting them

I have a large database of products and everyday I wish to run a script that moves rows with active = '1' into a new table and deletes the original开发者_如何学C row.

But I can't seem to find the appropriate Mysql command to accomplish the migration.

It would be great if someone could shed some light on the situation.

Thanks a lot

You should be able to complete this with the following

CREATE TABLE NewTable LIKE OldTable;

INSERT INTO NewTable 
SELECT * FROM OldTable WHERE Active = 1;

DELETE FROM OldTable WHERE Active = 1;

Just as curiosity, what is the point of doing this? If you're worried about the fuzzy rows you could do the delete as follows

DELETE OldTab FROM OlTable AS OldTab
INNER JOIN NewTable AS NewTab
ON OldTab.ID = NewTab.ID

without any details, here is what you will do:

create an INSERT statement to the new table AS SELECT from the old table WHERE active = 1.

create a DELETE statement that deletes from the first table any rows it finds in the second table.

commit;