81

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.

Marty Wallace
  • 34,046
  • 53
  • 137
  • 200
  • I believe your question has been previously answered: http://stackoverflow.com/questions/2343644/in-mysql-how-to-copy-the-content-of-one-table-to-another-table-within-the-same – Andrew - OpenGeoCode Nov 06 '13 at 20:25

8 Answers8

149

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';
luckydonald
  • 5,976
  • 4
  • 38
  • 58
Nelson
  • 49,283
  • 8
  • 68
  • 81
  • 44
    Warning ! this needs to be done in a transaction otherwise you could be losing some data – jfgrang Dec 03 '14 at 15:29
  • 3
    Another option if you are using auto incrementing id's and are affecting the entire table.

    SELECT MAX(id) INTO @max_id FROM TABLE_1 WHERE ....
    INSERT INTO TABLE_2 SELECT * FROM TABLE_1 WHERE id<=@max_id AND ....
    DELETE FROM TABLE_1 WHERE id<=@max_id AND ....
     – John Congdon Jan 13 '15 at 17:28
  • 2
    Recommend hard-copying your columns (i.e. `INSERT INTO person_table (a,b,c) SELECT a,b,c FROM customer_table...` – user1032531 Oct 03 '19 at 17:55
  • @jfgrang: If the target table is empty beforewards, would it be possible to **not use a transaction** and do a `DELETE customer_table FROM customer_table INNER JOIN persons_table ON ` instead of the DELETE from the answer? – Bowi Mar 09 '23 at 12:32
41
    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";
BigJacko
  • 393
  • 3
  • 4
CodeMonkey
  • 1,087
  • 2
  • 15
  • 24
37

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

Robert R.
  • 631
  • 6
  • 6
6
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.

Jake Pucan
  • 628
  • 8
  • 11
5
BEGIN;
INSERT INTO persons_table select * from customer_table where person_name = 'tom';
DELETE FROM customer_table where person_name = 'tom';
COMMIT;
lukyer
  • 7,595
  • 3
  • 37
  • 31
1

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)
Fabio Antunes
  • 22,251
  • 15
  • 81
  • 96
escube
  • 81
  • 1
  • 1
  • 1
    Suggested edit instead as comment (by Abent) "=> The delete query as proposed may be very heavy and take a very long execution time, better use the repeat where clause solution (much more lighter)" – NoDataDumpNoContribution Sep 17 '14 at 08:11
  • This solution is very bad. IN is slow, very slow. – sigi Jun 11 '17 at 20:16
  • The DELETE might be much faster this way: (assuming that both tables have an index on id) `DELETE t1 FROM table1 t1 JOIN table2 t2 ON (t2.id=t1.id)` – Mark1 Dec 15 '17 at 13:23
1

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;
Rakesh Chaudhari
  • 3,310
  • 1
  • 27
  • 25
0

These two ways are well to move data from one table to another by deleting the data from the first table. But remember in this case the first table's id which is auto_increment will not rearrange the id so in this case might be if your table has billions of data and still increasing then it might be not right for you:

A simple case with providing a range of id from which you want to move your data:

    INSERT INTO old_authors SELECT * FROM authors WHERE id > 1000;
    DELETE FROM authors WHERE id > 1000;

In case if you want to find data with date range then try this one:

INSERT INTO old_authors SELECT * FROM authors WHERE DATE_FORMAT(added, "%Y-%m-%d") > "2022-05-01";
DELETE FROM authors WHERE DATE_FORMAT(added, "%Y-%m-%d") > "2022-05-01";

Also, use Transection and commit to:

START TRANSACTION;
INSERT INTO old_authors SELECT * FROM authors WHERE DATE_FORMAT(added, "%Y-%m-%d") > "2022-04-24";
DELETE FROM authors WHERE DATE_FORMAT(added, "%Y-%m-%d") > "2022-04-24";
COMMIT;
heySushil
  • 493
  • 7
  • 13