SQL – Alter command – Explanations, Syntax and examples

Once a table has been created, renaming it or changing the datatypes are common. You may also prefer to add or delete columns in a table. For this purpose, SQL provides ALTER TABLE statement to alter the characteristics of the table.

Alter Table

In SQL, ALTER TABLE statement is primarily used to add or delete columns, modify existing columns, rename a table or column, changing the data types, sizes and so on.

1) Rename table

SQL allows to overwrite the old name of the table and rename it using the syntax below:

-- SYNTAX

ALTER TABLE old_table_name
      RENAME TO new_table_name;
      
      
-- EXAMPLE

ALTER TABLE codingeek 
      RENAME TO codingeeksql;

In the above example, the name of the table is changed from “codingeek” to “codingeeksql”


2) Rename column

You can rename the existing column or columns in a table using the syntax as follows:

-- SYNTAX

ALTER TABLE table_name
  	  RENAME COLUMN old_column_name TO new_column_name;  


-- EXAMPLE

ALTER TABLE codingeeksql 
	    RENAME COLUMN city TO place;

Here, the name of the specified column is changed from “city” to “place” in codingeeksql table.


3) Add new column

One can add a new column to an existing table by mentioning the name and datatype of the column.

Consider the table “codingeeksql” as shown below:

IDNameAge
 1riya 20
 2neha 22

Add a new column “Area” with type varchar(15) as (syntax along with example):

-- SYNTAX

ALTER TABLE table_name
  	  ADD new_column_name datatype[(size)];

--EXAMPLE

ALTER TABLE codingeeksql
      ADD Area VARCHAR2(15);

Output:

IDNameAgeArea
 1riya 20
 2neha 22

An empty field is added to the table where the name of the column is Area and type is varchar


4) Adding multiple columns

Similarly, you can add multiple columns to an existing table by defining it using the folllowing syntax:

 
-- SYNTAX

ALTER TABLE table_name
  	ADD ( column_name1 datatype[(size)],
              column_name2 datatype[(size)],
              column_name3 datatype[(size)],
	      ...
        );


-- EXAMPLE

ALTER TABLE codingeeksql
  ADD (Occupation VARCHAR2(30),
	     Address VARCHAR2(50),
       Phone int
  );
 

Output:

IDNameAgeAreaOccupationAddressPhone
 1riya 20
 2neha 22

Thus, the empty columns OccupationAddress, Phone are created with datatypes varchar, varchar and integer respectively.


5) Modify existing columns

You can modify the characteristics of an existing table columns such as datatype, size, NOT NULL:

Current Structure of the codingeeksql table is:

Name                        Null?       Type
--------------------------- --------    ------
 ID                         NOT NULL     INT
 NAME                        NULL      VARCHAR(10)
 AREA                        NULL      VARCHAR(15)
 OCCUPATION                  NULL      VARCHAR(30)
 ADDRESS                     NULL      VARCHAR(50)
 PHONE                       NULL         INT

Below is the syntax and example to change the datatype to CHAR(20).

-- SYNTAX

ALTER TABLE table_name
  	  MODIFY column_name column_datatype[(size)];

-- EXAMPLE

ALTER TABLE codingeeksql
      MODIFY Area CHAR(20);
 

Now see the changes in the structure:

Name                        Null?      Type
--------------------------- --------  ------
 ID                         NOT NULL    INT
 NAME                         NULL    VARCHAR(10)
 AREA                         NULL    CHAR(20)
 OCCUPATION                   NULL    VARCHAR(30)
 ADDRESS                      NULL    VARCHAR(50)
 PHONE                        NULL      INT

The datatype of Area is changed from varchar to char and size from 15 to 20.


6) Modify multiple existing columns

We can modify multiple columns in a similar manner. The syntax for modifying multiple columns is given as:

-- SYNTAX

ALTER TABLE table_name
	MODIFY (column_name1 column_datatype[(size)],
          column_name2 column_datatype[(size)],
          column_name3 column_datatype[(size)],
          ....
    );


-- EXAMPLE

ALTER TABLE codingeeksql
	MODIFY (ID SMALLINT,
          NAME CHAR(13)
    );
 

The modifications in the datatype and size can be seen below:

Name                         Null?        Type
--------------------------- --------     ------
 ID                         NOT NULL    SMALLINT
 NAME                         NULL      CHAR(13)
 AREA                         NULL      CHAR(20)
 OCCUPATION                   NULL      VARCHAR(30)
 ADDRESS                      NULL      VARCHAR(50)
 PHONE                        NULL        INT

Modifications:

  • The datatype of ID is modified from INT to SMALLINT
  • The datatype and size of Name is changed from VARCHAR(10) to CHAR(13)

7) Drop column

Drop column is a synonym of deleting column in the table. Using DROP statement you can delete a particular column using the following syntax:

-- SYNTAX

ALTER TABLE table_name
  	DROP COLUMN column_name;


--EXAMPLE

ALTER TABLE codingeeksql 
      DROP COLUMN ID;
 
NameAgeAreaOccupationAddressPhone
riya 20
neha 22

From the codingeeksql table, the ID field is removed completely or “dropped” and the table is displayed with remaining fields.


Learning never exhausts the mind. Keep learning!!

Do share and subscribe and comment below for any suggestion, query or opinion.
Keep Coding! Happy Coding 🙂

Recommended -