Move rows from one table to another in MySQL?

MySQLMySQLi Database

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

The syntax is as follows −

insert into yourDestinationTableName select *from yourOriginalTable where someCondition

To understand the above syntax. let us create a table. The following is the query to create a table −

mysql> create table StudentTable
   −> (
      −> Id int,
      −> Name varchar(100)
   −> );
Query OK, 0 rows affected (0.65 sec)

Now, I will create second table. The query is as follows −

mysql> create table Employee
   −> (
      −> EmployeeId int
      −> ,
      −> EmployeeName varchar(100)
   −> );
Query OK, 0 rows affected (0.54 sec)

Inserting some records in the table Employee. The query to insert records is as follows −

mysql> insert into Employee values(1,'Carol');
Query OK, 1 row affected (0.18 sec)

mysql> insert into Employee values(2,'John');
Query OK, 1 row affected (0.16 sec)

mysql> insert into Employee values(3,'Johnson');
Query OK, 1 row affected (0.11 sec)

Now you can display all records from Employee table with the help of SELECT statement. The query is as follows:

mysql> select *from Employee;

The following is the output −

+------------+--------------+
| EmployeeId | EmployeeName |
+------------+--------------+
|          1 | Carol        |
|          2 | John         |
|          3 | Johnson      |
+------------+--------------+
3 rows in set (0.00 sec)

Implement the syntax we discussed in the beginning to move row in another table. The below query moves row from Employee table to StudentTable −

mysql> insert into StudentTable select *from Employee where EmployeeId = 3 and EmployeeName = 'Johnson';
Query OK, 1 row affected (0.17 sec)
Records: 1 Duplicates: 0 Warnings: 0

Now, you can check whether the row is present in second table ‘StudentTable’ or not. The query is as follows −

mysql> select *from StudentTable;

The following is the output −

+------+---------+
| Id   |    Name |
+------+---------+
| 3    | Johnson |
+------+---------+
1 row in set (0.00 sec)

Look at the above sample output, we have moved row from one table to another. To move all the rows, you just need to remove the “where” condition. The query is as follows −

mysql> insert into StudentTable select *from Employee;
Query OK, 3 rows affected (0.15 sec)
Records: 3 Duplicates: 0 Warnings: 0

The query displays all the updated records in the StudentTable −

mysql> select *from StudentTable;

The following is the output −

+------+---------+
| Id   | Name    |
+------+---------+
| 1    | Carol   |
| 2    | John    |
| 3    | Johnson |
+------+---------+
3 rows in set (0.00 sec)
raja
Published on 10-Jan-2019 11:16:08
Advertisements