View source for Updating and Deleting MySQL Tables

<table border="0" cellspacing="0" width="100%"><tr>
<td width="20%">[[Creating Databases and Tables Using SQL Commands|Previous]]<td align="center">[[MySQL Essentials|Table of Contents]]<td width="20%" align="right">[[Inserting Data into a MySQL Database|Next]]</td>
<tr>
<td width="20%">Creating Databases and Tables Using SQL Commands<td align="center"><td width="20%" align="right">Inserting Data into a MySQL Database</td>
</table>
<hr>


<htmlet>mysql</htmlet>


Once a table has been created it is inadvisable to modify it once it contains data. Sometimes, however, it is necessary to make a change and such changes can be made using the SQL statements. It is also possible to delete an existing table from a database. In this chapter both of these topics will be covered.

== Altering a MySQL Table ==
<htmlet>adsdaqbox_flow</htmlet>
A pre-existing table in a database may be modified using the SQL ''ALTER TABLE'' statement. The ''ALTER TABLE'' statement allows the structure of a table to be modified. Permitted changes include the addition or removal of columns, changing the data type of a column and renaming of the table or columns.

MySQL typically performs an alteration by copying the table to a temporary table, making changes to the temporary table and then deleting the original table and renaming the temporary table to the replace it.

The ''CREATE TABLE'' syntax requires the name of the table to be altered, followed by the list of changes that are to be made to the table:

ALTER TABLE ''table_name'' ''alteration_spec'', ''alteration_spec'', ....

=== Adding and Deleting Table Columns ===

The following example adds a column named ''product_description'' to a table named ''products'':

<pre>
ALTER TABLE products ADD product_description CHAR(20);
</pre>

To specify the new column is to be the first column in a table:

<pre>
ALTER TABLE products ADD product_description CHAR(20) FIRST;
</pre>

To add a new column so that appears after a specific column:

<pre>
ALTER TABLE products ADD product_description CHAR(20) AFTER product_name;
</pre>

A column can be removed from a table using the ''DROP COLUMN'' keywords together with the name of the columns to be removed. For example:

<pre>
ALTER TABLE products DROP COLUMN product_description;
</pre>

=== Renaming Tables and Columns ===

Both the name of a table and the names of columns within a table may be renamed. 

To rename a table:

<pre>
RENAME TABLE products TO new_products;
</pre>

Alternatively this can be achieved using the ''ALTER TABLE'' statement:

<pre>
ALTER TABLE customer RENAME customer2;
</pre>

The renaming of a column can be achieved using the ''CHANGE'' keyword together with the old and new column names followed by the current datatype of the column. For example to change a column of type INTEGER named ''customer_id'' to ''new_customer_id'':

<pre>
ALTER TABLE customer CHANGE customer_name new_customer_name INTEGER;
</pre>

=== Changing the Data Type of a Column ===

The current data type of a column may be changed using the ''ALTER TABLE'' statement in conjunction with the ''MODIFY'' keyword. The following example changes the data type of a column named ''prod_id'' from INT to TINYINT:

<pre>
ALTER TABLE products MODIFY prod_id TINYINT;
</pre>

Similarly, the following command increases the length of a column named ''prod_description'' to 20 characters:

<pre>
ALTER TABLE products MODIFY prod_description CHAR(20);
</pre>

== Deleting a Database Table ==

A table may be deleted from a database using the ''DROP TABLE'' statement. All that is required with this statement is the name of the table to be deleted. The deletion is permanent and cannot be undone:

<pre>
DROP TABLE customers;
</pre>

<htmlet>mysql</htmlet>

<hr>
<table border="0" cellspacing="0" width="100%"><tr>
<td width="20%">[[Creating Databases and Tables Using SQL Commands|Previous]]<td align="center">[[MySQL Essentials|Table of Contents]]<td width="20%" align="right">[[Inserting Data into a MySQL Database|Next]]</td>
<tr>
<td width="20%">Creating Databases and Tables Using SQL Commands<td align="center"><td width="20%" align="right">Inserting Data into a MySQL Database</td>
</table>