SQL Delete Column from Table

When working with an SQL database, it’s common to want to delete a column. It could be that the column is no longer relevant, or you accidentally added it when creating your table. Whatever your reason, deleting columns from a table is straightforward. Besides, SQL allows deletion of multiple columns in a single command.

This guide covers all details about deleting columns from a table. In summary, we will create a table and then show how to delete columns.

How to Delete a Column from Table

Before we open our SQL, we first need to ensure we have MySQL running. Confirm its status using the below command.

If it’s not running, use systemctl to start it.

Let us open our MySQL on the command line with the below command.

For this tutorial, we will use the linuxhint database. So, select your database using the use command.

Once we select the database, let us create the users table with various columns. In our case, we have four columns.

We can use the INSERT command to insert values into our columns.

So far, we can check the status of our table using the SELECT * command. In the image below, you will note that we have various columns and the values that we inserted into the columns.

Now that we have our table ready, it is time we focused on how to delete columns.

To delete a column from a table, the ALTER command is used. The command supports modifying the structure of tables, including adding columns, modifying them, renaming, and deleting columns.

Below is the syntax for deleting columns from a table.

You must add the drop keyword to delete your table. For instance, if we wanted to delete column lastname from table users, we could execute the SQL command below.

Once we’ve dropped the column, we can use the desc command to see the new structure. The output below shows the success message after we drop the column and the remaining columns after running the desc command.

Another way to confirm that the dropped column is deleted is by selecting all the columns and their values using the select all command, as in the image below. You will note we do not have the column lastname.

Deleting Multiple Columns

Let us first use the ADD keyword to add more columns to our table to demonstrate how we can delete multiple columns.

Let us check the current structure of the table using the desc command.

To delete multiple columns in one command, add the drop keyword for each column name separated by a command.

Here is the syntax for deleting multiple columns.

In the example below, we are dropping column age and height. We can confirm the deletion by using the desc command.

Note that you don’t have to add the column keyword when using SQL. Using the syntax below, you can also delete a column in a table.

Conclusion

The ALTER keyword is used to delete a column in SQL. This guide has presented various practical usage examples of deleting columns in a table, including deleting multiple columns using one command. Besides, we’ve seen how to confirm the deletion using the desc keyword. Hopefully, you can now work with tables in SQL and use the ALTER command to modify their structure.