Syntax error How to rearrange MySQL columns?

How to rearrange MySQL columns?

To rearrange the MySQL columns, check the column arrangement with the help of show create command. The syntax is as follows −

show create table yourTableName;

The syntax to rearrange the MySQL columns is as follows −

alter table yourTableName change column yourColumnName yourColumnName dataType first

For the same purpose, you can use the after keyword. The syntax is as follows −

alter table yourTableName change column yourColumnName yourColumnName dataType after yourSpecificColumnName;

Let us first check the column arrangement for the already created table “AddColumn” −

mysql> show create table AddColumn;

The following is the output −

+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                    |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| AddColumn | CREATE TABLE `addcolumn` (`StudentId` varchar(50) DEFAULT NULL, `StudentName` varchar(300) DEFAULT NULL ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci|
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now you can rearrange the StudentName column before StudentId. The query is as follows −

mysql> alter table AddColumn change StudentName StudentName varchar(300) first;
Query OK, 0 rows affected (1.28 sec)
Records: 0 Duplicates: 0 Warnings: 0

Here is the query that can be used to check the StudentName is first column or not −

mysql> desc AddColumn;

The following is the output displaying that the columns are successfully rearranged −

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| StudentName | varchar(300) | YES  |     | NULL    |       |
| StudentId   | varchar(50)  | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Updated on: 2019-07-30T22:30:24+05:30

641 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements