mysql - move rows from one table to another
MysqlMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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';
Solution 2 - Mysql
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";
Solution 3 - Mysql
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
Solution 4 - Mysql
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.
Solution 5 - Mysql
BEGIN;
INSERT INTO persons_table select * from customer_table where person_name = 'tom';
DELETE FROM customer_table where person_name = 'tom';
COMMIT;
Solution 6 - Mysql
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)
Solution 7 - Mysql
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;