Mysql 4: ALTER

Written by: , 2009-04-16 18:07:25

Sometimes you need to alter table och change the the content. These are two differant actions but with the same basic need; to alter a table in some way.

Altering the structure of a table

We will use this table:

+--------------------+--------------------+--------------------+

|store_name          |store_sales         |store_updates       |

+____________________+____________________+____________________+

|Hanks Hardware       2500                 2009-01-01 00:00:00 |

|Santos SuperStore    750                  2008-12-01 00:00:00 |

|Bobs Boutique        1050                 2009-01-01 00:00:00 |

|Pattyïs Pantstore    1050                 2008-12-15 00:00:00 |

|Hanks Hardware       2250                 2008-01-01 00:00:00 |

+--------------------------------------------------------------+

If we'd like to enter a new column to this table, store_location for example, we can use the ALTER TABLE command:

Syntax:

ALTER TABLE table_name
ADD column_name data_type

Example:

ALTER TABLE stores 

ADD store_location VARCHAR(150)

+--------------------+--------------------+--------------------+--------------------+

|store_name          |store_sales         |store_updates       |store_location      |

+____________________+____________________+____________________+____________________+

|Hanks Hardware       2500                 2009-01-01 00:00:00  NULL                |

|Santos SuperStore    750                  2008-12-01 00:00:00  NULL                |

|Bobs Boutique        1050                 2009-01-01 00:00:00  NULL                |

|Pattyïs Pantstore    1050                 2008-12-15 00:00:00  NULL                |

|Hanks Hardware       2250                 2008-01-01 00:00:00  NULL                |

+-----------------------------------------------------------------------------------+

Well, this is a problem, now we have NULL as a value in every record, that's no good. It's better to use the DEFAULT keyword to set a default value for every record in the column you're adding:

Syntax:
ALTER TABLE table_name
ADD column_name data_type
DEFAULT default_value

Example:

ALTER TABLE stores 

ADD store_location VARCHAR(150) 

DEFAULT 'Stockholm'

+--------------------+--------------------+--------------------+--------------------+

|store_name          |store_sales         |store_updates       |store_location      |

+____________________+____________________+____________________+____________________+

|Hanks Hardware       2500                 2009-01-01 00:00:00  Stockholm           |

|Santos SuperStore    750                  2008-12-01 00:00:00  Stockholm           |

|Bobs Boutique        1050                 2009-01-01 00:00:00  Stockholm           |

|Pattyïs Pantstore    1050                 2008-12-15 00:00:00  Stockholm           |

|Hanks Hardware       2250                 2008-01-01 00:00:00  Stockholm           |

+----------------------------------------------------------------------------------+

This is alot better, now the stores have a default value. The only problem now is that Santos SuperStore isn't located in Stockholm, it's located in Oslo. Damnit! Well, good thing we have the UPDATE TABLE command that we can use:

Syntax:

UPDATE table_name
SET change
WHERE condition

Example:

UPDATE stores 

SET store_location = 'Oslo' 

WHERE store_name='Santos SuperStore'

+--------------------+--------------------+--------------------+--------------------+

|store_name          |store_sales         |store_updates       |store_location      |

+____________________+____________________+____________________+____________________+

|Hanks Hardware       2500                 2009-01-01 00:00:00  Stockholm           |

|Santos SuperStore    750                  2008-12-01 00:00:00  Oslo                |

|Bobs Boutique        1050                 2009-01-01 00:00:00  Stockholm           |

|Pattyïs Pantstore    1050                 2008-12-15 00:00:00  Stockholm           |

|Hanks Hardware       2250                 2008-01-01 00:00:00  Stockholm           |

+-----------------------------------------------------------------------------------+

As we can see, Santos SuperStore now has the correct location value in our table. If we hadn't used the WHERE clause, all records would now be located in Oslo.

These are the basics of altering a table, more subitems with more specific walkthroughs will come up.

There are no comments on this article.

Leave a message!

If you have any question or just want to leave a message, just fill out the form below!

Your e-mail will not be visible in your post, it is for validation reasons only

Name:
E-mail:
Comment: