SQL ALTER TABLE (ADD/DROP/MODIFY) Command

As discussed in previous articles, we are aware that the changes to a table or column can be done using ALTER TABLE command. The ALTER TABLE can be even used to add or drop the table or the columns or even it can be used to modify the constraints of the existing table or columns.

ALTER TABLE (ADD) –

The ADD command is used to add columns to the existing table. There is no need to build the entire table again if we can easily use the command to add columns to the existing one.

  • Syntax of ALTER TABLE (ADD) –

ALTER TABLE table_name ADD
(Column1 datatype , column2 datatype, column3 datatype,..., columnN datatype);

 

Demo table –

Manager’s Table

Region

Regional Manager

West

Sadie Pawthorne

East

Chuck Magee

Central

Roxanne Rodriguez

South

Fred Suzuki

 

Example – Consider the Manager’s table of the Customer database, add a column as “Category ID” using ALTER TABLE (ADD).

ALTER TABLE [dbo].[Manager] ADD Manager_id integer ;

 

The output after implementing the above ALTER TABLE (ADD) operator  –

Region

Regional Manager

Manager_id

West

Sadie Pawthorne

NULL

East

Chuck Magee

NULL

Central

Roxanne Rodriguez

NULL

South

Fred Suzuki

NULL

 

“Manager_id” is added to the manager table using ALTER ADD Command. But currently, the values are NULL in the new column. That is because we haven’t inserted any value into the column.

 

ALTER TABLE (DROP) –

The DROP command is used to drop any column in the table. It is used to delete the not needed columns.

  • Syntax of ALTER TABLE (DROP) –

ALTER TABLE table_name DROP COLUMN column_name ;

 

Demo table –

Region

Regional Manager

Manager_id

West

Sadie Pawthorne

NULL

East

Chuck Magee

NULL

Central

Roxanne Rodriguez

NULL

South

Fred Suzuki

NULL

 

Example – Consider the Manager’s table of the Customer database, drop the created column “Manager_id ” using ALTER TABLE (DROP).

ALTER TABLE [dbo].[Manager] DROP COLUMN Manager_id; 

 

The output after implementing the above ALTER TABLE (DROP) operator  –

Region

Regional Manager

West

Sadie Pawthorne

East

Chuck Magee

Central

Roxanne Rodriguez

South

Fred Suzuki

 

“Manager_id” is dropped from the manager table using ALTER DROP Command.

 

ALTER TABLE (MODIFY) –

The MODIFY command is used to MODIFY any column or its constraint in the table. MODIFICATION can be done on multiple columns at once.

Syntax of ALTER TABLE (MODIFY) –

ALTER TABLE table_name
ALTER COLUMN column_name column_type ;

 

Demo table –

Region

Regional Manager

West

Sadie Pawthorne

East

Chuck Magee

Central

Roxanne Rodriguez

South

Fred Suzuki

 

Example – Consider the Manager’s table of the Customer database, and modify the datatype of the Regional manager.

ALTER TABLE [dbo].[Manager]
ALTER COLUMN [Regional Manager] varchar (20);

After executing the above query maximum size of the Regional manager Column is reduced to 20 from 30.

Other SQL topics to check out:

Follow us on TwitterFacebookLinkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.

Automate data analysis pipeline and create report ready dashboards

If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.

#analytics #data #business #artificialintelligence #machinelearning #startup #deeplearning #deeplearning #datascience #ai #growth #dataanalytics #india #datascientist #powerbi #dataanalysis #tableau #SQL #businessanalytics #businessanalyst #businessandmanagement #dataanalyst #businessanalysis #analyst #analysis #powerbideveloper #powerbidesktop #letsviz