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
Easy way to re-order columns in MySQL?
To re-order columns in MySQL, use the ALTER TABLE MODIFY COLUMN. The syntax is as follows -
ALTER TABLE yourTableName MODIFY COLUMN yourColumnName data type after yourColumnName.
To understand the above syntax, let us first create a table. The query to create a table is as follows.
mysql> create table reOrderColumn -> ( -> ProductId int, -> DeliveryDate datetime, -> ProductName varchar(100) -> ); Query OK, 0 rows affected (0.76 sec)
Now check the description of the table. The query is as follows.
mysql> desc reOrderColumn;
The following is the output.
+--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | ProductId | int(11) | YES | | NULL | | | DeliveryDate | datetime | YES | | NULL | | | ProductName | varchar(100) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 3 rows in set (0.10 sec)
Now re-order the column using ALTER MODIFY command. I will reorder the DeliveryDate column after the ProductName column. The query is as follows.
mysql> alter table reOrderColumn modify column DeliveryDate datetime after ProductName; Query OK, 0 rows affected (1.61 sec) Records: 0 Duplicates: 0 Warnings: 0
Let us now check the column have been reordered or not. The query is as follows.
mysql> desc reOrderColumn;
The following is the output displaying the columns have been reordered.
+--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | ProductId | int(11) | YES | | NULL | | | ProductName | varchar(100) | YES | | NULL | | | DeliveryDate | datetime | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
Updated on: 30-Jul-2019
7K+ Views
Advertisements