Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql - move rows from one table to another

Tags:

mysql

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.

like image 680
Marty Wallace Avatar asked Nov 06 '13 20:11

Marty Wallace


People also ask

How do I move rows from one table to another in MySQL?

You can move rows from one table to another with the help of INSERT INTO SELECT statement.

How do I move records from one table to another?

The SQL INSERT INTO SELECT Statement The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.

How do I copy data from one table to another table in MySQL?

The first statement creates a new table new_table in the destination database (destination_db) by duplicating the existing table (existing_table) from the source database (source_db). The second statements copy data from the existing table in the source database to the new table in the destination database.

How do I move data from one database to another in MySQL?

Below are the steps you can follow to migrate MySQL database between 2 servers: Step 1: Backup the Data. Step 2: Copy the Database Dump on the Destination Server. Step 3: Restore the Dump.


2 Answers

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';
like image 87
Nelson Avatar answered Oct 10 '22 02:10

Nelson


    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";
like image 40
CodeMonkey Avatar answered Oct 10 '22 01:10

CodeMonkey