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

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

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

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

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       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

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

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

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

Examples related to mysql