SQL

Add Columns, Delete Columns, and Edit Columns, and Find Love Using SQL ALTER Statements

Previously, we covered creating tables in SQL. To create a column in a table, use the ALTER TABLE statement, along with the ADD and then define a column name followed by the data type:

ALTER TABLE nameoftable
ADD nameofcolumn datatype

To delete a column in a table::

ALTER TABLE nameoftable
DROP COLUMN nameofcolumn

To change the type of data stored in a column, use MODIFY:

ALTER TABLE nameoftable
MODIFY COLUMN nameofcolumn datatype

In this article, we’ll use the same “Customers” table from our first articles to add and edit columns:

ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US

Let’s add a column called “ExpDate” in the “Customers” table with a date type, which will oddly hold a date. To do so, use the following SQL statement:

ALTER TABLE Customers
ADD ExpDate date

The new columns are empty. Once you’ve created a column, you can then change the data type of that column.Let’s just change this to year, assuming all the ExpDate fields have the same actual day for their expiration:

ALTER TABLE Customers
ALTER COLUMN ExpDate year

Crap, I promised love. How about this, give all the things away for free. Now let’s delete the column:

ALTER TABLE Customers
DROP COLUMN ExpDate

Obviously, this would delete all of the data in the column as well, so be careful!