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.

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

score 139 · Accepted Answer · edited Jul 23 '18 at 19:29

139

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

edited Jul 23 '18 at 19:29

luckydonald

5,148
3
36
51

answered Nov 06 '13 at 20:11

Nelson

46,753
8
64
79

41

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

score 39 · Answer 2 · edited Jan 15 '14 at 11:38

39

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

edited Jan 15 '14 at 11:38

BigJacko

383
3
4

answered Nov 06 '13 at 20:12

CodeMonkey

1,057
2
15
24

18

I guess this should be wrapped in transaction. Otherwise this operation wouldn't be atomic. – Dimitry K Feb 27 '14 at 09:52

score 34 · Answer 3 · answered Jul 01 '15 at 09:02

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

Should really lock the writes to the table in question? – Jack B Nov 22 '18 at 16:30 — Jack B, Nov 22 '18 at 16:30

score 6 · Answer 4 · answered Jul 29 '15 at 04:13

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.

score 5 · Answer 5 · answered Feb 11 '17 at 12:36

5

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

answered Feb 11 '17 at 12:36

lukyer

6,747
2
35
29

score 1 · Answer 6 · edited Jun 17 '14 at 14:08

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)

edited Jun 17 '14 at 14:08

Fabio Antunes

21,224
15
78
95

answered Jun 17 '14 at 13:51

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)" – Trilarion 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

score 1 · Answer 7 · answered Apr 28 '18 at 05:58

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;

answered Apr 28 '18 at 05:58

Rakesh Chaudhari

2,962
1
24
23

1

you need to fix your casing, it's either "IN" or "in", not "In". – Buffalo Aug 28 '19 at 14:04

mysql - move rows from one table to another

7 Answers7

Linked

Related