将行从一个表移动到另一个表

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.

将从select查询中确定行集。

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

我想将行从customer表移动到person表

Ideally removing the data from the original table, but this wouldnt be a deal breaker.

理想情况下，从原始表中删除数据，但这不会破坏交易。

7 个解决方案

#1

A simple INSERT INTO SELECT statement:

一个简单的插入到SELECT语句:

INSERT INTO persons_table select * from customer_table where person_name = 'tom';

DELETE FROM customer_table where person_name = 'tom';

#2

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

#3

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

@N在开始时获取时间戳，用于两个命令。一切都在交易中，以确保没有人感到不安

#4

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.

您还可以使用ORDER BY、LIMIT和ASC/DESC来限制和选择要移动的特定列。

#5

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

#6

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

要使用这个解决方案，源表和目标表必须是相同的，并且必须在第一个表中具有唯一的id和自动增量(这样相同的id就不会被重用)。

Lets say table1 and table2 have this structure

假设表1和表2有这个结构

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

#7

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;

#1