Alter Query Command in SQL, Add, Modify and Drop Column in Table
ALTER QUERY SQL
The SQL ALTER TABLE explanation is utilized to include, adjust, or drop/erase sections in a table. The SQL ALTER TABLE proclamation is likewise used to rename a table.
ADD COLUMN IN TABLE
Syntax
To add a column in a table, the SQL ALTER TABLE syntax would be:
Alter table tbl_name add col_name col_definition;
Example
For instance, let’s add a column name SID in our USERS table. This column will be holding an integer value
Alter table USERS add SID int(11);
In the above example, the SQL ALTER TABLE will add a column called SID to the USERS table.
ADD MULTIPLE COLUMNS IN TABLE
Syntax
For adding multiple columns in an existing table, following SQL ALTER TABLE syntax will be used:
Alter table tbl_name
Add (col_1 column-definition, col_2 column-definition, …, col_n col_definition);
Example
For instance, let’s add a column name Name, UName, PWord and Status in our USERS table. These columns will be holding varchar values
Alter table USERS add (Name varchar(20), UName varchar(20), PWord varchar(25), Status as varchar(8));
In the above example, SQL Alter Query will add three columns, Name as varchar(20), UName as varchar(20), PWord as varchar(25) and Status as varchar(8) in USERS table
MODIFY COLUMN IN TABLE
Syntax
Different databases have different implementation of modify column command and hence variate in syntax. For modifying a column in an existing table, following syntax for different DBMS solutions are used.
For MySQL, MariaDB and Oracle Databases
Alter table tbl_name modify col_name col_type;
For SQL Server Database:
Alter table tbl_name alter column col_name col_type;
For PostgreSQL Database:
Alter table tbl_name alter column col_name type col_definition;
Example
Let’s try the above syntax one by one and see how they modify a column called Status using the alter table statement.
For MySQL, MariaDB and Oracle Databases:
Alter table USERS modify STATUS int(11) not null;
For SQL Server:
Alter table USERS alter column STATUS int(11) not null;
For PostgreSQL:
Alter table USERS
alter column STATUS type int(11)
alter column set not null;
MODIFY MULTIPLE COLUMNS IN TABLE
Syntax
To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is:
For Oracle Database:
Alter table tbl_name modify (col_1 col_type, col_2 col_type, …, col_n col_type);
For MySQL and MariaDB Database:
Alter table tbl_name
modify (col_1 col_def [first | after col_name],
modify (col_2 col_def [first | after col_name],
…
modify (col_n col_def [first | after col_name];
For PostgreSQL Database:
Alter table tbl_name
Alter column col_1_name type col_1_def,
Alter column col_2_name type col_2_def,
…
Alter column col_2_name type col_2_def;
Example
Let’s try an example that uses the ALTER TABLE statement for the modification of more than one column. In this example, two columns called Name and UName from the USERS table will be modified.
For Oracle Database:
Alter table USERS modify (Name varchar(20) not null, UName varchar(20) not null);
For MariaDB and MySQL Databases:
Alter table USERS modify Name varchar(20) not null, modify UName varchar(20) not null);
For PostgreSQL:
Alter table USERS
alter column Name type varchar(20),
alter column Name set not null,
alter column UName type varchar(20),
alter column UName set not null;
DROP COLUMN IN TABLE
Syntax
To drop or delete a column from an existing table, the following SQL ALTER TABLE syntax will be used:
Alter table tbl_name drop column col_name;
Example
Let’s try an example for dropping a column “Status” from our USERS table. The syntax would be:
Alter table USERS drop column Status;
More Related Articles For You
- Architecture of DBMS
- CODDS RULE DBMS
- Database Models in DBMS
- Relational DBMS Concepts
- Keys and Types of keys in Database
- Database Normalization
- Generalization, Specialization and Aggregation Concepts in DBMS
- ERD Diagram Tutorial with Examples in DBMS
- Introduction to SQL
- How to Create Query in SQL, Create Table, Delete Table and User Insert Info Statements
- TCL Commands in SQL
- Truncate Query, Drop and Rename Query in SQL
- All DML Statement in SQL, Select Statement, Delete, Insert and Update Statement
- Data Control Language DCL Revoke and Grant Command in SQL
- Select Statement or Select Query in SQL Explained with Examples
- Distinct Keyword Explained in SQL
- WHERE Statement or WHERE Clause in SQL Statement Explained
- AND & OR Operators in SQL
- LIKE Operator in SQL
- ORDER BY Clause Sorting Explained in SQL
- Group By Clause in SQL
- Having Clause Explained in SQL
- SQL Constraints
- SQL Aggregate Functions
- SQL Aliases Use and Purpose in SQL
- SQL Joins Types, Use and Purpose
- SQL Sequence Syntax and Use with Examples
- SQL View
- SET Operation in SQL, UNION, UNION ALL, Intersect and Minus