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
How to add columns at specific position in existing table in MySQL?
To add columns at a specific position in existing table, use after command. The syntax is as follows −
ALTER TABLE yourTableName ADD COLUMN yourColumnName data type AFTER yourExistingColumnName;
To understand the above syntax, let us first create a table. The query to create a table is as follows.
mysql> create table changeColumnPosition -> ( -> Id_Position1 int, -> Name_Position2 varchar(100), -> Address_Position4 varchar(200) -> ); Query OK, 0 rows affected (0.53 sec)
Now you can check the description of existing table using desc command. The syntax is as follows −
desc yourTableName;
The following is the query to check the description.
mysql> desc changeColumnPosition;
The following is the output.
+-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | Id_Position1 | int(11) | YES | | NULL | | | Name_Position2 | varchar(100) | YES | | NULL | | | Address_Position4 | varchar(200) | YES | | NULL | | +-------------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
I have three columns and I want to add another column after Name_Position2 Field. The query is as follows.
mysql> alter table changeColumnPosition -> add Age_Position3 int after Name_Position2; Query OK, 0 rows affected (1.34 sec) Records: 0 Duplicates: 0 Warnings: 0
We added column Age_Position3 successfully after the Name_Position2. Check the existing table again now for the changes we did above.
mysql> desc changeColumnPosition;
The following is the output.
+-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | Id_Position1 | int(11) | YES | | NULL | | | Name_Position2 | varchar(100) | YES | | NULL | | | Age_Position3 | int(11) | YES | | NULL | | | Address_Position4 | varchar(200) | YES | | NULL | | +-------------------+--------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
Updated on: 25-Jun-2020
6K+ Views
Advertisements