CODEWITHSUNDEEP
mysql
Marty Wallace
Marty Wallace

Reputation: 35734

mysql - move rows from one table to another

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           [email protected]


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.

Upvotes: 84

Views: 149797

Answers (8)

heySushil
heySushil

Reputation: 499

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;

Upvotes: 0

Nelson Benítez León
Nelson Benítez León

Reputation: 50563

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

Upvotes: 155

Rakesh Chaudhari
Rakesh Chaudhari

Reputation: 3502

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;

Upvotes: 1

lukyer
lukyer

Reputation: 7983

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

Upvotes: 5

Jake Pucan
Jake Pucan

Reputation: 646

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.

Upvotes: 5

Robert R.
Robert R.

Reputation: 681

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

Upvotes: 39

escube
escube

Reputation: 81

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)

Upvotes: 1

CodeMonkey
CodeMonkey

Reputation: 1107

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

Upvotes: 41

Related Questions