mysql - move rows from one table to another

Question

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.

Answer 1

A simple INSERT INTO SELECT statement:

INSERT INTO persons_table SELECT * FROM customer_table WHERE person_name = 'tom';

---

DELETE FROM customer_table WHERE person_name = 'tom';