- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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
How to change the column position of MySQL table without losing column data?
You can change the column position of MySQL table without losing data with the help of ALTER TABLE command. The syntax is as follows −
ALTER TABLE yourTableName MODIFY yourColumnName1 data type AFTER yourColumnName2;
To understand the above concept, let us create a table. The query to create a table with some columns is as follows −
mysql> create table changeColumnPositionDemo −> ( −> StudentId int, −> StudentAddress varchar(200), −> StudentAge int, −> StudentName varchar(200) −> ); Query OK, 0 rows affected (0.72 sec)
Let us insert some data in the table. The query to insert records is as follows -.
mysql> insert into changeColumnPositionDemo values(101,'US',23,'Johnson'); Query OK, 1 row affected (0.13 sec) mysql> insert into changeColumnPositionDemo values(102,'UK',20,'John'); Query OK, 1 row affected (0.19 sec) mysql> insert into changeColumnPositionDemo values(103,'US',22,'Carol'); Query OK, 1 row affected (0.39 sec) mysql> insert into changeColumnPositionDemo values(104,'UK',19,'Sam'); Query OK, 1 row affected (0.18 sec)
Now you can display all records with the help of select statement. The query is as follows −
mysql> select *from changeColumnPositionDemo;
The following is the output -
+-----------+----------------+------------+-------------+ | StudentId | StudentAddress | StudentAge | StudentName | +-----------+----------------+------------+-------------+ | 101 | U | 23 | Johnson | | 102 | UK | 20 | John | | 103 | US | 22 | Carol | | 104 | UK | 19 | Sam | +-----------+----------------+------------+-------------+ 4 rows in set (0.00 sec)
Here is the query to change the column position without losing data. We are shifting “StudentAddress” column to be after “StudentAge” column −
mysql> ALTER TABLE changeColumnPositionDemo MODIFY StudentAddress varchar(200) AFTER StudentAge; Query OK, 0 rows affected (2.27 sec) Records: 0 Duplicates: 0 Warnings: 0
Above we have set the column StudentAddress after column name StudentAge.
The following is the query to check whether the above two columns have been changed or not without losing data −
mysql> select *from changeColumnPositionDemo;
The following is the output −
+-----------+------------+----------------+-------------+ | StudentId | StudentAge | StudentAddress | StudentName | +-----------+------------+----------------+-------------+ | 101 | 23 | US | Johnson | | 102 | 20 | UK | John | | 103 | 22 | US | Carol | | 104 | 19 | UK | Sam | +-----------+------------+----------------+-------------+ 4 rows in set (0.00 sec)
- Related Articles
- How to sort an R data frame column without losing row names?
- How can we change the data type of the column in MySQL table?
- How to clear table formatting style without losing table data in Excel?
- How to repeat the values stored in a data column of MySQL table?
- How to change MySQL column definition?
- How to change format of dates in a table column with a MySQL query?
- Update column data without using temporary tables in MySQL?
- How can I change the name of an existing column from a MySQL table?
- Change the column name from a MySQL table with Student record?
- How do I select data from one table only where column values from that table match the column values of another table in MySQL?
- How to modify the size of column in MySQL table?
- How to find the position of a data frame’s column value based on a column value of another data frame in R?
- How to alter the data type of a MySQL table’s column?
- How can column data values of a table be compared using MySQL STRCMP() function?
- How to export specific column data in MySQL?
