How To Drop A Column In SQL

For any SQL user, you will know that the commands are the primary focus. They need to be written correctly and executed properly in order to perform their function.

How to drop a column using SQL commands is fairly uncomplicated, once you get the hang of the syntax.

This guide will take an explorative path through the inner workings of SQL and end up at how to drop a column from a database.

How To Drop A Column In SQL

What Is SQL: An Overview

SQL is also known as Structured Query Language.

It is a technical group of commands that control different elements and apply different inputs to relational databases.

SQL is a language in its own right, created in the 1970s when the need arose to manipulate different data sets within relational databases.

It has a range of commands and a range of subcategories such as DDL, DQL, and DML, which we will explore a bit more later on.

Relational Databases: An Overview

Relational databases contain information that correlates to other information within the database.
They are one of the most common types of database and have been around since the 1970s, the same as SQL.

Relational databases are used to store finite pieces of data that relate in a certain way. This can be names, addresses, ages, birthdates, gender, and so on.

Common SQL Commands

There are various ways in which you can manipulate data with SQL. These are known as SQL Statements, which can be a single statement and contain a key constraint.

ALTER TABLE

This allows you to add in or get rid of, or change pre-existing table columns.

DROP TABLE

This allows you to delete a whole table from the database.

DROP COLUMN

This allows you to delete a specific column from a table within the database.

INSERT

This allows you to insert rows and columns or tables.

UPDATE

This allows you to update information in a specific cell or table.

SQL Categories: A Brief Look

SQL Categories A Brief Look

The DROP command is a DDL SQL command.

DDL stands for Data Definition Language.

These are the commands in SQL that define the data. With these commands, you can delete, create, and change the structures within the database such as tables and columns.

The main DDL command is ‘INSERT’.

DQL stands for Data Query Language.

These are the commands in SQL that query the data. With these commands, you can select different values of data from the database that you want to work with, or change.

The main DQL command is ‘SELECT’.

DML stands for Data Manipulation Language. These are the commands in SQL that manipulate the data.

With these commands, you can control the data in the database. The main DML commands are ‘INSERT’, ‘UPDATE’, and ‘DELETE’.

DCL stands for Data Control Language. These are the commands in SQL that control the data. With these commands, you can control access to the database. The main DCL command is ‘GRANT’.

How To Drop A Column In SQL

When we say ‘drop’ a column in SQL, we are referring to the deletion process of deleting a column from a table within the relational database.

As is expected with databases, sometimes you will need to remove columns or a single column and therefore pieces of data from the overall structure.

There is a way to do this without having to delete the entire table and start again by re-entering all of the lost data.

To update a table and drop a column, you need to know the syntax that guides it. This is called the TABLE DROP COLUMN statement or the DROP COLUMN clause.

But let’s run through a few basics about columns and tables before we get onto that part.

  1. You will need to know the column names that you want to drop.
  2. A column can have various definitions. These are known as column definitions or column values. There is a more detailed syntax to reference these that we will also get to soon.
  3. Any standard table will have a set number of columns.
  4. There can be more than one table in a single database.
  5. Therefore, it is important to include the table name into the table statement so that you alter the correct target table.
  6. Columns can be named the same thing in multiple tables. Again, more evidence as to why the name of the target table is a crucial part of your SQL statement.

The Syntax

This is a two-stage process and can be used to change a single table structure.

ALTER TABLE Table Name

The alter table statement is an instruction for the target table.

DROP COLUMN Column Name

The drop column statement is an instruction to delete the named column from the table and therefore the database.

Altogether, this looks like ALTER TABLE DROP COLUMN.

When used in the context of a database, it is vital that you include both the name of the table and the column name as well. Otherwise, the command will be null, which means it will not work.

Dropping Multiple Columns

Sometimes you might need to delete more than one column.
This is no more complicated than deleting a single column.

All you have to do is add to the syntax. It will look like this.
ALTER TABLE table name DROP COLUMN (columnname1, columnname2, columnname3)…

And so on, and so on. Up to and including the total number of target columns that you want to delete from the database.

Step By Step

Now we have covered the basics, the next stage is to list the steps.

  1. Step One is to find your table name. This is the table from which you wish to drop a column.
  2. Step Two is to identify the column name that you want to delete.
  3. Type the Syntax command, including any and all names that you are deleting and the source table that you are editing.

An Example

To put this into practice, we will now take a look at an example of the DROP COLUMN SQL statement.
ALTER TABLE Patients DROP TABLE appointments

Our Conclusion

You can see that SQL is a multi-faceted instructional language. It can be used to control and change all the data and structural components of relational databases.

To drop a column in SQL, all you need to know is the syntax behind it. This, plus the source table name, and the name of the column or columns that you are trying to delete.

Albert Niall
Latest posts by Albert Niall (see all)