How to Drop Column from MariaDB Table

In this MariaDB tutorial, we will learn about the “MariaDB drop column”, we will see a few examples of how to drop a column from a MariaDB table. Also, we will cover the following topics.

  • How to drop column if exists in MariaDB
  • MariaDB drop column from table
  • MariaDB drop virtual column
  • How to drop multiple columns in MariaDB

MariaDB drop column from table

To drop a column from a MariaDB table, we will use the DROP COLUMN with ALTER TABLE statement.

Run the below query to drop the column Canada from a table drop_col.

ALTER TABLE drop_col
DROP COLUMN Canada;
MariaDB drop column from table
MariaDB drop column from table

We have dropped the column successfully from a table drop_col as we can see in the output.

This is how to drop a column from a MariaDB table.

MariaDB drop column if exists

The DROP COLUMN option is used with ALTER TABLE statement to drop the column from a MariaDB table. IF EXISTS option is used with DROP COLUMN to avoid the error generated when a column didn’t exist.

The syntax is given below.

ALTER TABLE drop_col
DROP COLUMN IF EXISTS col_name;

Let’s create the table as drop_col.

CREATE TABLE drop_col(United_States varchar(10),United_Kindom varchar
(10), Canada varchar(10), New_Zealand varchar(10));

In the above code, we are creating the table as drop_col with columns that denote the country name like United States, United Kindom, etc.

MariaDB drop column if exists
MariaDB drop column if exists

Now, we are going to drop one of the columns from a table drop_col using the below code.

ALTER TABLE drop_col
DROP COLUMN IF EXISTS United_States;

Here in the code, we are removing or dropping the column United_States from the table drop_col using the DROP COLUMN IF EXISTS with ALTER TABLE statement.

How to drop column if exists in MariaDB
MariaDB Drop column if exists

The output shows that the United_State column dropped from the table.

Also, check: How to Add Column in MariaDB

MariaDB drop virtual column

In MariaDB, The virtual column exists in the virtual table, a table that exists till the current session, and this kind of table is created from another table using the CREAT VIEW statement.

Let’s create a view from a table change_dtype using the below query.

CREATE VIEW demo_view AS SELECT * FROM change_dtype;

Here, we are creating the view as demo_view from the table change_dtype.

MariaDB drop virtual column
MariaDB drop virtual column

Unfortunately, there is no command like DROP COLUMN for view in MariaDB, but we can get the necessary column by creating a new view.

Let’s suppose that we want to drop the column col_name from the table demo_view, so here we cannot drop the column directly instead we will create the new view with the specific column.

CREATE VIEW new_view AS SELECT id FROM change_type;
How to drop virtual column in MariaDB
MariaDB drop virtual column

This is how to drop a virtual column in MariaDB.

Read: How to Grant User Access to a MariaDB Database

MariaDB drop multiple columns

The ALTER TABLE with DROP COLUMN can also drop the multiple columns from a table. All we have to do is to use the keyword DROP COLUMN for each column like we want to drop the two columns, then DROP COLUMN will be used two times with ALTER TABLE statement for each column.

The syntax is given below.

ALTER TABLE table_name
DROP COLUMN col_1,
DROP COLUMN col_2,
DROP COLUMN col_3;

Now we are going to drop the three columns from a table drop_col using the below code.

ALTER TABLE drop_col
DROP COLUMN United_States,
DROP COLUMN Canada,
DROP COLUMN New_Zealand;
MariaDB drop multiple columns
MariaDB drop multiple columns

Now, only one column is left in the table because we have removed the multiple or three columns from a drop_col MariaDB table.

This is how to drop multiple columns from a MariaDB table.

You may also like to read the following tutorials on MariaDB.

So, in this tutorial, we have learned how to delete a column from a table in MariaDB and covered the following topics.

  • MariaDB drop column
  • MariaDB drop column if exists
  • MariaDB drop column from table
  • MariaDB drop virtual column
  • MariaDB drop multiple columns