Now we all know ALTER TABLE declaration will make changes, modify values, remove or even alter columns in our traditional database table while already working on an existing table. So now in this article, we will see how to add a new column to an existing table using the ALTER TABLE statement in MySQL.
Syntax
ALTER TABLE TableName ADD NewColumn Column_Definition First or After ColumnName;
Column_Definition: It is simply the data type we are gonna define in the column if it is to be null or not null for example.
First or After: This clause is to tell after which column the new column is to be added this is completely optional as by default it adds a new column to the last without this clause.
Example: Adding Multiple Columns In A Table
In this article, we will perform ALTER TABLE and add two columns to our existing table crypto_price_list and use the update to set some values to the newly added column.
Here is our crypto_price_list table.
ALTER TABLE crypto_price_list ADD COLUMN Alt_Coin VARCHAR(20) NOT NULL AFTER Price, ADD COLUMN Year VARCHAR(20) NOT NULL AFTER Alt_Coin;
So here we have added two columns Alt_Coin and Year after each other columns with NOT NULL.
UPDATE crypto_price_list SET Alt_Coin = 'babydoge' WHERE ID = 2;
UPDATE crypto_price_list SET Year = '2021' WHERE ID = 2;
So after adding two columns we decided to update just the row with id 2 and update the newly added columns with some values to it using the UPDATE clause.