Bookmark and Share

SQL - Alter

SQL ALTER is the command used to add, edit, and modify data objects like tables, databases, and views. ALTER is the command responsible for making table column adjustments or renaming table columns. New table columns can also be added and dropped from existing SQL tables.

Advertise on Tizag.com

SQL Add:

USE mydatabase;

ALTER TABLE orders
ADD discount VARCHAR(10);

SQL Results:

idcustomerday_of_orderproductquantitydiscount
1Tizag2008-08-01 00:00:00.000Pen8NULL
2Tizag2008-08-01 00:00:00.000Stapler3NULL
3A+Maintenance2008-08-16 00:00:00.000Hanging Files14NULL
4Gerald Garner2008-08-15 00:00:00.00019" LCD Screen5NULL
5Tizag2008-07-25 00:00:00.00019" LCD Screen5NULL
6Tizag2008-07-25 00:00:00.000HP Printer4NULL

As you can see from the results panel, SQL has added an additional column, discount, to the orders table. Since this column was just created, it contains no data, and only NULL values have been returned.

SQL - Alter Table: Modify Column

SQL table columns can be altered and changed using the MODIFY COLUMN command. This allows the developer the opportunity to mold table columns or adjust settings as needed.

SQL Modify Column:

USE mydatabase;

ALTER TABLE orders
ALTER COLUMN discount DECIMAL(18,2);

Above, we have modified the new discount table column changing the column data type from a varchar to a decimal table column. This example can be expanded to modify table columns as needed by the developer.

SQL - SQL Alter Table: Drop

This column can be deleted using the SQL DROP command. Once this column has been dropped, however, the data stored inside of it will be lost forever. Proceed with caution!

SQL Drop Column Code:

USE mydatabase;

ALTER TABLE orders
DROP COLUMN discount;
Bookmark and Share




Found Something Wrong in this Lesson?

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!

Advertise Here

More Tutorials!
Microsoft Office Tutorials Artist Tutorials