MorkaLork Development

Interesting stuff I've picked up over the years...

Mysql 4: ALTER

2009-04-16 18:07:25 | 253 views | change structure table alter content add remove update

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.




Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.