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

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes

Search

Shortcodes Ultimate

 
Follow Us
 
BGP