[mysql] mysql - move rows from one table to another

If i have two tables that are identical in structure, how can i move a set of rows from 1 table to the other?

The set of rows will be determined from a select query.

for example:

customer table

person_id | person_name | person_email
123         tom           [email protected]


persons table

person_id | person_name  | person_email

a sample select would be:

select * from customer_table where person_name = 'tom';

I want to move the row from customer table to person table

Ideally removing the data from the original table, but this wouldnt be a deal breaker.

BEGIN;
INSERT INTO persons_table select * from customer_table where person_name = 'tom';
DELETE FROM customer_table where person_name = 'tom';
COMMIT;

I had to solve the same issue and this is what I used as solution.

To use this solution the source and destination table must be identical, and the must have an id unique and autoincrement in first table (so that the same id is never reused).

Lets say table1 and table2 have this structure

|id|field1|field2

You can make those two query :

INSERT INTO table2 SELECT * FROM table1 WHERE

DELETE FROM table1 WHERE table1.id in (SELECT table2.id FROM table2)

To move and delete specific records by selecting using WHERE query,

BEGIN TRANSACTION;
Insert Into A SELECT * FROM B where URL="" AND email ="" AND Annual_Sales_Vol="" And OPENED_In="" AND emp_count=""  And contact_person= "" limit 0,2000;
delete from B where Id In (select Id from B where URL="" AND email ="" AND Annual_Sales_Vol="" And OPENED_In="" AND emp_count="" And contact_person= "" limit 0,2000);
commit;

INSERT INTO Persons_Table (person_id, person_name,person_email)
      SELECT person_id, customer_name, customer_email
      FROM customer_table
      ORDER BY `person_id` DESC LIMIT 0, 15 
      WHERE "insert your where clause here";
DELETE FROM customer_table
      WHERE "repeat your where clause here";

You can also use ORDER BY, LIMIT and ASC/DESC to limit and select the specific column that you want to move.

    INSERT INTO Persons_Table (person_id, person_name,person_email)
          SELECT person_id, customer_name, customer_email
          FROM customer_table
          WHERE "insert your where clause here";
    DELETE FROM customer_table
          WHERE "repeat your where clause here";

The answer of Fabio is really good but it take a long execution time (as Trilarion already has written)

I have an other solution with faster execution.

START TRANSACTION;
set @N := (now());
INSERT INTO table2 select * from table1 where ts < date_sub(@N,INTERVAL 32 DAY);
DELETE FROM table1 WHERE ts < date_sub(@N,INTERVAL 32 DAY);
COMMIT;

@N gets the Timestamp at the begin and is used for both commands. All is in a Transaction to be sure nobody is disturbing