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)

Rishi Rathor

Published on 10-Jan-2019 12:32:26

• Related Questions & Answers
• 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 change MySQL column definition?
• How to repeat the values stored in a data column of MySQL table?
• How to alter the data type of a MySQL table’s column?
• How can I change the name of an existing column from a MySQL table?
• How to modify the size of column in MySQL table?
• Change the column name from a MySQL table with Student record?
• Update column data without using temporary tables in MySQL?
• How to change format of dates in a table column with a MySQL query?
• How to use REPLACE() function with column’s data of MySQL table?
• 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 reload the current page without losing any form data with HTML?
• How to find the position of a data frame’s column value based on a column value of another data frame in R?
• How can we modify column/s of MySQL table?