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           tom example com   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

User · Answer

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)

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

BEGIN  INSERT INTO persons table select   from customer table where person name    tom   DELETE FROM customer table where person name    tom   COMMIT

User · Answer

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  lt  date sub  N INTERVAL 32 DAY   DELETE FROM table1 WHERE ts  lt  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

User · Answer

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

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

Examples related to mysql