[go: up one dir, main page]
Scribd
Upload
31 views4 pages

Alter

The SQL ALTER TABLE statement is used to add, delete, or modify columns and constraints in an existing table. It provides syntax for adding a column, dropping a column, renaming a column, and altering a column's datatype. Examples illustrate how to implement these changes in a 'Customers' and 'Persons' table.

Uploaded by

soha.adel66
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
31 views4 pages

Alter

The SQL ALTER TABLE statement is used to add, delete, or modify columns and constraints in an existing table. It provides syntax for adding a column, dropping a column, renaming a column, and altering a column's datatype. Examples illustrate how to implement these changes in a 'Customers' and 'Persons' table.

Uploaded by

soha.adel66
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

SQL ALTER TABLE Statement

SQL ALTER TABLE Statement


The ALTER TABLE statement is used to add, delete, or modify columns in an
existing table.

The ALTER TABLE statement is also used to add and drop various constraints on
an existing table.

ALTER TABLE - ADD Column


To add a column in a table, use the following syntax:

ALTER TABLE table_name


ADD column_name datatype;

The following SQL adds an "Email" column to the "Customers" table:

ExampleGet your own SQL Server


ALTER TABLE Customers
ADD Email varchar(255);

ALTER TABLE - DROP COLUMN


To delete a column in a table, use the following syntax (notice that some
database systems don't allow deleting a column):

ALTER TABLE table_name


DROP COLUMN column_name;

The following SQL deletes the "Email" column from the "Customers" table:
Example
ALTER TABLE Customers
DROP COLUMN Email;

ALTER TABLE - RENAME COLUMN


To rename a column in a table, use the following syntax:

ALTER TABLE table_name


RENAME COLUMN old_name to new_name;

To rename a column in a table in SQL Server, use the following syntax:

SQL Server:

EXEC sp_rename 'table_name.old_name', 'new_name', 'COLUMN';

ALTER TABLE - ALTER/MODIFY


DATATYPE
To change the data type of a column in a table, use the following syntax:

SQL Server / MS Access:

ALTER TABLE table_name


ALTER COLUMN column_name datatype;

My SQL / Oracle (prior version 10G):

ALTER TABLE table_name


MODIFY COLUMN column_name datatype;

Oracle 10G and later:

ALTER TABLE table_name


MODIFY column_name datatype;
ADVERTISEMENT

SQL ALTER TABLE Example


ID LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Look at the "Persons" table:

Now we want to add a column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons


ADD DateOfBirth date;

Notice that the new column, "DateOfBirth", is of type date and is going to hold a
date. The data type specifies what type of data the column can hold. For a
complete reference of all the data types available in MS Access, MySQL, and
SQL Server, go to our complete Data Types reference.

The "Persons" table will now look like this:


ID LastName FirstName Address City DateOfBirth

ID
1 LastName
Hansen Ola FirstName Timoteivn 10Address Sandnes City

12 Hansen
Svendson Tove Ola Borgvn 23 Timoteivn 10
Sandnes Sandnes

23 Svendson
Pettersen Kari Tove Storgt 20 Borgvn 23 Stavanger Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Change Data Type Example


Now we want to change the data type of the column named "DateOfBirth" in the
"Persons" table.

We use the following SQL statement:

ALTER TABLE Persons


ALTER COLUMN DateOfBirth year;

Notice that the "DateOfBirth" column is now of type year and is going to hold a
year in a two- or four-digit format.

DROP COLUMN Example


Next, we want to delete the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons


DROP COLUMN DateOfBirth;

The "Persons" table will now look like this:

You might also like