ALTER TABLE statement is used to add, delete or modify columns in an existing table.
Alter Table Add Column
1-- syntax
2ALTER TABLE tablename
3ADD column1 datatype;
4
5-- it will add "address" column to the "engineer" table
6ALTER TABLE engineer
7ADD address varchar(255);
Alter Table Alter/Modify Column
1-- SQL Server / MS Access:
2ALTER TABLE tablename
3ALTER COLUMN column1 datatype;
4
5-- PostgreSQL :
6ALTER TABLE tablename
7ALTER COLUMN column1 [SET DATA] TYPE newdatatype;
8
9-- MariaDB :
10ALTER TABLE tablename
11 ADD column1 columndefinition
12 [ FIRST | AFTER column1 ];
13
14-- My SQL / Oracle (prior version 10G):
15ALTER TABLE tablename
16MODIFY COLUMN column1 datatype;
17
18-- Oracle 10G and later:
19ALTER TABLE tablename
20MODIFY column1 datatype;
Alter Table Drop Column
1-- syntax
2ALTER TABLE tablename
3DROP COLUMN column1;
4
5-- it will delete "phone" column to the "engineer" table
6ALTER TABLE engineer
7DROP COLUMN phone;
Examples / Outputs
1-- add new column
2ALTER TABLE certificate
3ADD details varchar(255);
| id |
engineerid |
certificatename |
certificatedate |
details |
| 1 |
1 |
Docker Certified Associate |
2020-05-05 |
|
| 2 |
2 |
Certified Kubernetes Administrator |
2021-07-03 |
|
| 3 |
2 |
Certified Jenkins Engineer |
2022-01-03 |
|
1-- change datatype
2ALTER TABLE certificate
3ALTER COLUMN details text;
1-- drop column
2ALTER TABLE certificate
3drop COLUMN details;
| id |
engineerid |
certificatename |
certificatedate |
| 1 |
1 |
Docker Certified Associate |
2020-05-05 |
| 2 |
2 |
Certified Kubernetes Administrator |
2021-07-03 |
| 3 |
2 |
Certified Jenkins Engineer |
2022-01-03 |