Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
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)
3K+ Views
