Alter query in sql (add, drop, modify & change column)

Blog Bustle 04/03/2020

alter query in sql is used to do some changes in table like add column, drop column, modify column, change column name and many more. Remember that before running alter query in sql, first we have to select database.

Different syntax of alter query in sql:

1. Add column alter query in sql

We can add one or more columns in table using single alter query in sql.

Add one column

ALTER TABLE table_name
ADD column_name column_datatype;

You can also write "ADD column column_name column_datatype" in place of "ADD column_name column_datatype". Both are correct.

Add multiple column

ALTER TABLE table_name
ADD column1_name column1_datatype,
ADD column2_name column2_datatype,
ADD column3_name column3_datatype,
.....
ADD columnN_name columnN_datatype;

2. Drop column alter query in sql

We can drop one or more columns in table using single alter query in sql.

DROP one column

ALTER TABLE table_name
DROP COLUMN column_name;

You can also write "DROP column column_name" in place of "DROP column_name". Both are correct.

DROP multiple column

ALTER TABLE table_name
DROP column1_name,
DROP column2_name,
DROP column3_name,
.....
DROP columnN_name;

3. Modify column alter query in sql

We can modify one or more columns in table using single alter query in sql.

Modify one column

ALTER TABLE table_name
MODIFY column_name new_datatype;

You can also write "Modify column column_name new_datatype" in place of "MODIFY column_name new_datatype". Both are correct.

Modify multiple column

ALTER TABLE table_name
MODIFY column1_name new_datatype,
MODIFY column2_name new_datatype,
MODIFY column3_name new_datatype,
.....
MODIFY columnN_name new_datatype;

4. Change column name alter query in sql

ALTER TABLE table_name CHANGE column_name column_new_name new_datatype;

alter query in sql