Total Pageviews

December 17, 2015

12/17/2015 10:43:00 PM
The ALTER TABLE command allows you to add, modify, or drop a column from an existing table.
Adding column(s) to a table

Syntax #1: To add a column to an existing table, the ALTER TABLE syntax is

ALTER TABLE table_name
ADD column_name column-definition;
For example:
ALTER TABLE supplier
ADD supplier_name varchar2(50);
This will add a column called supplier_name to the supplier table.

Syntax #2: To add multiple columns to an existing table, the ALTER TABLE syntax is

ALTER TABLE table_name
ADD (
column_1 column-definition,
column_2 column-definition,
...
column_n column_definition );
For example:
ALTER TABLE supplier
ADD (
supplier_name varchar2(50),
city varchar2(45) );
This will add two columns (supplier_name and city) to the supplier table.
Modifying column(s) in a table

Syntax #1: To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY column_name column_type;
For example:
ALTER TABLE supplier
MODIFY supplier_name varchar2(100) not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.
Syntax #2: To modify multiple columns in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
MODIFY (
column_1 column_type,
column_2 column_type,
...
column_n column_type );
For example:
ALTER TABLE supplier
MODIFY (
supplier_name varchar2(100) not null,
city varchar2(75));
This will modify both the supplier_name and city columns.

 
Related Posts Plugin for WordPress, Blogger...