Welcome! Log In Create A New Profile

Advanced

Alter Table

Posted by sk8tzz 
Announcements Last Post
Announcement SoC Curricula 09/30/2017 01:08PM
Announcement Demarcation or scoping of examinations and assessment 02/13/2017 07:59AM
Announcement School of Computing Short Learning Programmes 11/24/2014 08:37AM
Announcement Unisa contact information 07/28/2011 01:28PM
Alter Table
May 03, 2006 12:19PM
Hi..

I know how to use alter table to add a field. For example if I have a CUSTOMER TABLE like below

CREATE TABLE CUSTOMER
(
IDNO CHAR(22) PRIMARY KEY,
NAME CHAR(60),
SEX CHAR(1) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(256),
CELLNO CHAR(18)
)

This is an interesting question. But what if what to add a field "BIRTHDATE" in between 'AGE' and 'ADDRESS' not necessarily at the end.

Anyone any ideas, lectures with answers ?
Re: Alter Table
July 18, 2006 11:29AM
From the book, it seems that you have to create a new table and then use select into to populate the new table with your older data. That's what I'd do. (Until someone tells me a better way!!)
Re: Alter Table
August 01, 2006 12:18PM
Found it.. You use a alter table function with the AFTER or FIRST keyword. Tested it, and it works.

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
or MODIFY [COLUMN] create_definition
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or RENAME [AS] new_tbl_name
or table_options

ALTER TABLE allows you to change the structure of an existing table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change the comment for the table and type of the table.
Sorry, only registered users may post in this forum.

Click here to login