Syntax error Can we use ADD and CHANGE with ALTER Statement in MySQL?

Can we use ADD and CHANGE with ALTER Statement in MySQL?

Yes, we can use ADD and CHANGE with ALTER statement. Let us first create a table −

mysql> create table DemoTable
   -> (
   -> Name varchar(100),
   -> Age int
   -> );
Query OK, 0 rows affected (0.84 sec)

Now check the description of table.

mysql> desc DemoTable;

Output

This will produce the following output −

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Name  | varchar(100) | YES  |     | NULL    |       |
| Age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.25 sec)

Here is the query with ALTER to update a column name as well adding new columns −

mysql> alter table DemoTable
-> add column Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> add column CountryName varchar(100) after Age,
-> change column Name FirstName varchar(200);
Query OK, 0 rows affected (1.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now check the description of table once again −

mysql> desc DemoTable;

Output

This will produce the following output −

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| FirstName   | varchar(200) | YES  |     | NULL    |                |
| Age         | int(11)      | YES  |     | NULL    |                |
| CountryName | varchar(100) | YES  |     | NULL    |                |
| Id          | int(11)      | NO   | PRI | NULL    | auto_increment |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
Updated on: 2020-06-30T12:41:40+05:30

149 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements